1、数据库的增删改查
[root@vm3 ~]# mysql -uroot -padmin123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| atguigu |
| atguigu2 |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use atguigu2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [atguigu2]> show tables;
+--------------------+
| Tables_in_atguigu2 |
+--------------------+
| a1 |
| a2 |
+--------------------+
2 rows in set (0.00 sec)
MariaDB [atguigu2]> insert into a1 (id,name,age) values (1,'zhangsan',18);
Query OK, 1 row affected (0.00 sec)
MariaDB [atguigu2]> select * from a1;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 18 |
+------+----------+------+
1 row in set (0.00 sec)
MariaDB [atguigu2]> insert into a1 values (2,'lisi',28),(3,'laow');
ERROR 1136 (21S01): Column count doesn't match value count at row 2
MariaDB [atguigu2]> insert into a1 values (2,'lisi',28),(3,'laow','20');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [atguigu2]> select * from a1;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 18 |
| 2 | lisi | 28 |
| 3 | laow | 20 |
+------+----------+------+
3 rows in set (0.00 sec)
MariaDB [atguigu2]> describe a2;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | | | |
| age | int(11) | NO | | 0 | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
MariaDB [atguigu2]> select * from a1;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 18 |
| 2 | lisi | 28 |
| 3 | laow | 20 |
+------+----------+------+
3 rows in set (0.00 sec)
MariaDB [atguigu2]> insert into a2 (id,name,age) select * from a1;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [atguigu2]> select * from a2;
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | zhangsan | 18 |
| 2 | lisi | 28 |
| 3 | laow | 20 |
+----+----------+-----+
3 rows in set (0.00 sec)
MariaDB [atguigu2]> drop table a1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [atguigu2]> show tables;
+--------------------+
| Tables_in_atguigu2 |
+--------------------+
| a2 |
+--------------------+
1 row in set (0.00 sec)
MariaDB [atguigu2]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| atguigu |
| atguigu2 |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [atguigu2]> create database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
MariaDB [atguigu2]>
MariaDB [atguigu2]> create database abc;
Query OK, 1 row affected (0.00 sec)
MariaDB [atguigu2]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| atguigu |
| atguigu2 |
| mysql |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [atguigu2]> drop database abc;
Query OK, 0 rows affected (0.00 sec)
MariaDB [atguigu2]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| atguigu |
| atguigu2 |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [atguigu2]> select * from a2;
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | zhangsan | 18 |
| 2 | lisi | 28 |
| 3 | laow | 20 |
+----+----------+-----+
3 rows in set (0.00 sec)
MariaDB [atguigu2]> delete from a2 where id=3;
Query OK, 1 row affected (0.00 sec)
MariaDB [atguigu2]> select * from a2;
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | zhangsan | 18 |
| 2 | lisi | 28 |
+----+----------+-----+
2 rows in set (0.00 sec)
MariaDB [atguigu2]> delete from a2 where age between 23 and 30;
Query OK, 1 row affected (0.00 sec)
MariaDB [atguigu2]> select * from a2;
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | zhangsan | 18 |
+----+----------+-----+
1 row in set (0.00 sec)
MariaDB [atguigu2]> show tables
-> ;
+--------------------+
| Tables_in_atguigu2 |
+--------------------+
| a2 |
+--------------------+
1 row in set (0.00 sec)
MariaDB [atguigu2]> alter table a2 rename a1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [atguigu2]> show tables;
+--------------------+
| Tables_in_atguigu2 |
+--------------------+
| a1 |
+--------------------+
1 row in set (0.00 sec)
MariaDB [atguigu2]> dedcribe a1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dedcribe a1' at line 1
MariaDB [atguigu2]> describe a1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | | | |
| age | int(11) | NO | | 0 | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
MariaDB [atguigu2]> alter table a1 modify name char(20);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [atguigu2]> describe a1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(20) | YES | | NULL | |
| age | int(11) | NO | | 0 | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
MariaDB [atguigu2]> alter table a1 change name username char(50) not null default '';
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [atguigu2]> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(50) | NO | | | |
| age | int(11) | NO | | 0 | |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
MariaDB [atguigu2]> alter table a1 add time datetime;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [atguigu2]> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(50) | NO | | | |
| age | int(11) | NO | | 0 | |
| time | datetime | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
MariaDB [atguigu2]> alter table a1 add sex char(1) after age;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [atguigu2]> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(50) | NO | | | |
| age | int(11) | NO | | 0 | |
| sex | char(1) | YES | | NULL | |
| time | datetime | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
MariaDB [atguigu2]> alter table a1 drop time;
Query OK, 1 row affected (0.06 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [atguigu2]> describe a1;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(50) | NO | | | |
| age | int(11) | NO | | 0 | |
| sex | char(1) | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
MariaDB [atguigu2]> grant all on atguigu2.* to zhangli@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [atguigu2]> grant all on atguigu2.* to lisi@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [atguigu2]> show grants for zhangli@'%';
+--------------------------------------------------------------------------------------------------------+
| Grants for zhangli@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangli'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `atguigu2`.* TO 'zhangli'@'%' |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [atguigu2]> revoke drop,delete on atguigu2.* from lisi@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [atguigu2]> show grants for lisi@'%';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for lisi@% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lisi'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE, CREATE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `atguigu2`.* TO 'lisi'@'%' |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)C端：
[root@jone ~]# mysql -uzhangli -p123456 -h 192.168.0.152
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)
MariaDB [(none)]> set password=password('111111');
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> exit
Bye
[root@jone ~]# mysql -uzhangli -p123456 -h 192.168.0.152
ERROR 1045 (28000): Access denied for user 'zhangli'@'192.168.0.153' (using password: YES)
[root@jone ~]# mysql -uzhangli -p111111 -h 192.168.0.152
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> exit
Bye
[root@jone ~]# mysql -uzhangli -p111111 -h 192.168.0.152
ERROR 1045 (28000): Access denied for user 'zhangli'@'192.168.0.153' (using password: YES)
[root@jone ~]# mysql -uzhangli -p123456 -h 192.168.0.152
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
Óɱ¾µØϵͳÖÐÖ¹
\S
Kernel \r on an \m
Last login: Mon May 3 17:40:28 2021 from 192.168.0.111
wellcome!
[root@jone ~]# mysql -uzhangli -p123456 -h 192.168.0.152
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| atguigu2 |
+--------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> use atguigu2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [atguigu2]> show tables;
+--------------------+
| Tables_in_atguigu2 |
+--------------------+
| a1 |
+--------------------+
1 row in set (0.01 sec)
MariaDB [atguigu2]> exit
Bye
[root@jone ~]# mysql -ulisi -p123456 -h 192.168.0.152
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| atguigu2 |
+--------------------+
2 rows in set (0.01 sec)
2、数据库的主从同步
主数据库：
[root@vm3 mysql]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
log-bin=mysql-bin
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
-- 插入 -- 11,1 全部
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
log-bin=mysql-bin
server-id=10
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
~
~
~
~
~
~
~
~
~
~
~
"/etc/my.cnf" 19L, 599C 已写入
您在 /var/spool/mail/root 中有新邮件
[root@vm3 mysql]# service mariadb restart
Redirecting to /bin/systemctl restart mariadb.service
[root@vm3 mysql]# cd
[root@vm3 ~]#
[root@vm3 ~]#
[root@vm3 ~]# my
myisamchk mysqlbinlog mysqldumpslow mysql_setpermission
myisam_ftdump mysqlbug mysql_find_rows mysqlshow
myisamlog mysqlcheck mysql_fix_extensions mysqlslap
myisampack mysql_convert_table_format mysqlhotcopy mysqltest
my_print_defaults mysqld_multi mysqlimport mysql_tzinfo_to_sql
mysql mysqld_safe mysql_install_db mysql_upgrade
mysqlaccess mysqld_safe_helper mysql_plugin mysql_waitpid
mysqladmin mysqldump mysql_secure_installation mysql_zap
[root@vm3 ~]# mysql -uroot -padmin123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> grant replication slave on *.* to slave@'192.168.0.153' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 398 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> exit
Bye
[root@vm3 ~]# ip add
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 08:00:27:41:b3:12 brd ff:ff:ff:ff:ff:ff
inet 192.168.10.22/24 brd 192.168.10.255 scope global enp0s3
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fe41:b312/64 scope link
valid_lft forever preferred_lft forever
3: enp0s8: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 08:00:27:23:0c:bf brd ff:ff:ff:ff:ff:ff
inet 192.168.0.152/24 brd 192.168.0.255 scope global enp0s8
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fe23:cbf/64 scope link
valid_lft forever preferred_lft forever
[root@vm3 ~]#
[root@vm3 ~]#
[root@vm3 ~]#
[root@vm3 ~]# mysql -uroot -padmin123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> create database abc;
ERROR 1007 (HY000): Can't create database 'abc'; database exists
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| atguigu |
| atguigu2 |
| mysql |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> drop abc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'abc' at line 1
MariaDB [(none)]> drop database abc;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| atguigu |
| atguigu2 |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> create database abc;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| atguigu |
| atguigu2 |
| mysql |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> create database ttt;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| atguigu |
| atguigu2 |
| mysql |
| performance_schema |
| ttt |
+--------------------+
7 rows in set (0.00 sec)
MariaDB [(none)]> create database fff;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| atguigu |
| atguigu2 |
| fff |
| mysql |
| performance_schema |
| ttt |
+--------------------+
8 rows in set (0.00 sec)
MariaDB [(none)]> drop fff;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'fff' at line 1
MariaDB [(none)]> drop database fff;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> 备数据库：
MariaDB [(none)]> change master to master_user='slave',master_password='123456',master_host='192.168.0.152',master_log_file='mysql-bin.000002',master_log_pos=398;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.152
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 639
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1008
Last_Error: Error 'Can't drop database 'abc'; database doesn't exist' on query. Default database: 'abc'. Query: 'drop database abc'
Skip_Counter: 0
Exec_Master_Log_Pos: 398
Relay_Log_Space: 1066
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1008
Last_SQL_Error: Error 'Can't drop database 'abc'; database doesn't exist' on query. Default database: 'abc'. Query: 'drop database abc'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.152
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 639
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1008
Last_Error: Error 'Can't drop database 'abc'; database doesn't exist' on query. Default database: 'abc'. Query: 'drop database abc'
Skip_Counter: 0
Exec_Master_Log_Pos: 398
Relay_Log_Space: 1066
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1008
Last_SQL_Error: Error 'Can't drop database 'abc'; database doesn't exist' on query. Default database: 'abc'. Query: 'drop database abc'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.152
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 639
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1008
Last_Error: Error 'Can't drop database 'abc'; database doesn't exist' on query. Default database: 'abc'. Query: 'drop database abc'
Skip_Counter: 0
Exec_Master_Log_Pos: 398
Relay_Log_Space: 1066
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1008
Last_SQL_Error: Error 'Can't drop database 'abc'; database doesn't exist' on query. Default database: 'abc'. Query: 'drop database abc'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.152
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 639
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1008
Last_Error: Error 'Can't drop database 'abc'; database doesn't exist' on query. Default database: 'abc'. Query: 'drop database abc'
Skip_Counter: 0
Exec_Master_Log_Pos: 398
Relay_Log_Space: 1066
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1008
Last_SQL_Error: Error 'Can't drop database 'abc'; database doesn't exist' on query. Default database: 'abc'. Query: 'drop database abc'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [(none)]> exit
Bye
[root@jone mysql]# service mariadb restart
Redirecting to /bin/systemctl restart mariadb.service
[root@jone mysql]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.152
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 639
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1008
Last_Error: Error 'Can't drop database 'abc'; database doesn't exist' on query. Default database: 'abc'. Query: 'drop database abc'
Skip_Counter: 0
Exec_Master_Log_Pos: 398
Relay_Log_Space: 1910
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1008
Last_SQL_Error: Error 'Can't drop database 'abc'; database doesn't exist' on query. Default database: 'abc'. Query: 'drop database abc'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.152
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 639
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1008
Last_Error: Error 'Can't drop database 'abc'; database doesn't exist' on query. Default database: 'abc'. Query: 'drop database abc'
Skip_Counter: 0
Exec_Master_Log_Pos: 398
Relay_Log_Space: 1910
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1008
Last_SQL_Error: Error 'Can't drop database 'abc'; database doesn't exist' on query. Default database: 'abc'. Query: 'drop database abc'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.152
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 639
Relay_Log_File: mariadb-relay-bin.000005
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 639
Relay_Log_Space: 1109
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| atguigu |
| fff |
| mysql |
| performance_schema |
| test |
| ttt |
+--------------------+
8 rows in set (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| atguigu |
| mysql |
| performance_schema |
| test |
| ttt |
+--------------------+
7 rows in set (0.00 sec)
MariaDB [(none)]> set global sql_slave_skip_counter=1;
对于初始主备数据库不一致情况：（
忽略错误，继续同步
）
MariaDB [(none)]> set global sql_slave_skip_counter=1;
版权声明：本文为weixin_45537413原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。