实验篇1(数据库增删改查,主从同步)

  • Post author:
  • Post category:其他


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 版权协议,转载请附上原文出处链接和本声明。