1、配置主-从-从结构，并做各种测试
第一步，配置主库51
vim /etc/my.cnf
[root@51 mysql]# grep -v “^#” /etc/my.cnf | grep -v “^$”
[mysqld]
validate_password_policy = 0
validate_password_length = 6
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin=master51
server_id=51
binlog_format=”mixed”
[root@51 mysql]# systemctl restart mysqld
[root@51 mysql]# mysql -uroot -p123456
mysql> show master status;
+—————–+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+—————–+———-+————–+——————+——————-+
| master51.000001 | 154 | | | |
+—————–+———-+————–+——————+——————-+
1 row in set (0.00 sec)
mysql> create database test1;
Query OK, 1 row affected (0.08 sec)
mysql> grant all on test1.* to test@”192.168.4.50″ identified by “123456”;
Query OK, 0 rows affected, 1 warning (0.08 sec)
第二步，配置从库52
[root@52 mysql]# vim /etc/my.cnf
[root@52 mysql]# grep -v “^#” /etc/my.cnf | grep -v “^$”
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
validate_password_policy=0
validate_password_length=6
server_id=52
log-bin=master52
binlog_format=”mixed”
log_slave_updates
[root@52 mysql]# systemctl restart mysqld
[root@52 mysql]# mysql -uroot -p123456
mysql> grant replication slave on *.* to rep2@”192.168.4.53″ identified by “123456”;
mysql> change master to master_host=’192.168.4.51′,master_user=”rep1″,master_password=”123456″,master_log_file=”master51.000001″,master_log_pos=154;
mysql> start slave;
Query OK, 0 rows affected (0.16 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.51
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master51.000001
Read_Master_Log_Pos: 154
Relay_Log_File: 52-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master51.000001
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: 154
Relay_Log_Space: 523
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: 51
Master_UUID: f1c08610-b633-11e8-b1ed-52540061c690
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql> show master status;
+—————–+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+—————–+———-+————–+——————+——————-+
| master52.000001 | 448 | | | |
+—————–+———-+————–+——————+——————-+
1 row in set (0.00 sec)
第3步，配置从库53
[root@53 mysql]# vim /etc/my.cnf
[root@53 mysql]# grep -v “^#” /etc/my.cnf | grep -v “^$”
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
validate_password_policy=0
validate_password_length=6
server_id=53
[root@53 mysql]# systemctl restart mysqld
[root@53 mysql]# mysql -uroot -p123456
mysql> change master to master_host=”192.168.4.52″,master_user=”rep2″,master_password=”123456″,master_log_file=”master52.000001″,master_log_pos=448;
Query OK, 0 rows affected, 2 warnings (0.53 sec)
mysql> start slave;
Query OK, 0 rows affected (0.06 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.52
Master_User: rep2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master52.000001
Read_Master_Log_Pos: 448
Relay_Log_File: 53-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master52.000001
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: 448
Relay_Log_Space: 523
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: 52
Master_UUID: 3b85ac97-b635-11e8-b479-5254007aa2ad
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
第4步，做各种测试
（1）、正常测试，主库51建表，从库都能查到
主库51上面操作
mysql> create database test1;
mysql> grant all on test1.* to test@”192.168.4.50″ identified by “123456”;
客户端50远程登陆53查看
[root@50 myload]# mysql -h 192.168.4.53 -utest -p123456
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| test1 |
+——————–+
2 rows in set (0.00 sec)
（2）、从库上新建表test1，主库和另一个从库是不会复制的
接着上面53的操作
mysql> use test1;
mysql> create table d1(id int(5) unsigned auto_increment primary key,age int(2) not null default “18”,name char(10));
mysql> desc d1;
+——-+—————–+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+—————–+——+—–+———+—————-+
| id | int(5) unsigned | NO | PRI | NULL | auto_increment |
| age | int(2) | NO | | 18 | |
| name | char(10) | YES | | NULL | |
+——-+—————–+——+—–+———+—————-+
3 rows in set (0.01 sec)
mysql> insert into d1(age,name) values (“22″,”lucy”);
mysql> insert into d1(age,name) values (“18″,”lily”);
mysql> exit
登陆52上面去看一眼，库里面没有表
[root@50 myload]# mysql -h 192.168.4.52 -utest -p123456
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| test1 |
+——————–+
2 rows in set (0.00 sec)
mysql> use test1;
Database changed
mysql> show tables;
Empty set (0.00 sec)
（3）、接上面的操作，在主库51上面去建表，建一个和53一模一样的表，52可以正常复制过去，53报错了
[root@50 myload]# mysql -h 192.168.4.51 -utest -p123456
mysql> show databases
-> ;
+——————–+
| Database |
+——————–+
| information_schema |
| test1 |
+——————–+
2 rows in set (0.00 sec)
mysql> use test1;
Database changed
mysql> create table d1(id int(5) unsigned auto_increment primary key,age int(2) not null default “18”,name char(10));
mysql> insert into d1(age,name) values (“22″,”lucy”);
mysql> insert into d1(age,name) values (“25″,”tom”);
mysql> exit
登陆52看一眼，是正常的
[root@50 myload]# mysql -h 192.168.4.52 -utest -p123456
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| test1 |
+——————–+
2 rows in set (0.00 sec)
mysql> use test1;
Database changed
mysql> show tables;
+—————–+
| Tables_in_test1 |
+—————–+
| d1 |
+—————–+
1 row in set (0.00 sec)
mysql> select * from d1;
+—-+—–+——+
| id | age | name |
+—-+—–+——+
| 1 | 22 | lucy |
| 2 | 25 | tom |
+—-+—–+——+
2 rows in set (0.00 sec)
登陆53发现，53的还是53自己的表，没有变化
[root@50 myload]# mysql -h 192.168.4.53 -utest -p123456
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| test1 |
+——————–+
2 rows in set (0.00 sec)
mysql> use test1;
Database changed
mysql> show tables;
+—————–+
| Tables_in_test1 |
+—————–+
| d1 |
+—————–+
1 row in set (0.00 sec)
mysql> select * from d1;
+—-+—–+——+
| id | age | name |
+—-+—–+——+
| 1 | 22 | lucy |
| 2 | 18 | lily |
+—-+—–+——+
2 rows in set (0.00 sec)
mysql> show slave status\G;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation
ERROR:
No query specified
去53确认一下，确实报错了
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.52
Master_User: rep2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master52.000001
Read_Master_Log_Pos: 1822
Relay_Log_File: 53-relay-bin.000002
Relay_Log_Pos: 784
Relay_Master_Log_File: master52.000001
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: 1050
Last_Error: Error ‘Table ‘d1′ already exists’ on query. Default database: ‘test1’. Query: ‘create table d1(id int(5) unsigned auto_increment primary key,age int(2) not null default “18”,name char(10))’
Skip_Counter: 0
Exec_Master_Log_Pos: 913
Relay_Log_Space: 1897
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: 1050
Last_SQL_Error: Error ‘Table ‘d1′ already exists’ on query. Default database: ‘test1’. Query: ‘create table d1(id int(5) unsigned auto_increment primary key,age int(2) not null default “18”,name char(10))’
Replicate_Ignore_Server_Ids:
Master_Server_Id: 52
Master_UUID: 3b85ac97-b635-11e8-b479-5254007aa2ad
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 180912 14:20:48
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
报错之后，继续操作51，53不会同步
[root@50 myload]# mysql -h 192.168.4.51 -utest -p123456
mysql> create database test2;
ERROR 1044 (42000): Access denied for user ‘test’@’192.168.4.50’ to database ‘test2’
mysql> use test1;
mysql> create table d2(name char(20),age int(5));
mysql> insert into d2 values(“lupo”,12),(“tyu”,16);
mysql> select * from d2;
+——+——+
| name | age |
+——+——+
| lupo | 12 |
| tyu | 16 |
+——+——+
2 rows in set (0.00 sec)
mysql> exit
Bye
[root@50 myload]# mysql -h 192.168.4.53 -utest -p123456
mysql> use test1;
Database changed
mysql> show tables;
+—————–+
| Tables_in_test1 |
+—————–+
| d1 |
+—————–+
1 row in set (0.01 sec)
重新停止，配置从库53，才能正常
mysql> stop slave;
Query OK, 0 rows affected (0.07 sec)
mysql> change master to master_host=”192.168.4.52″,master_user=”rep2″,master_password=”123456″,master_log_file=”master52.000001″,master_log_pos=2300;
Query OK, 0 rows affected, 2 warnings (0.48 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.52
Master_User: rep2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master52.000001
Read_Master_Log_Pos: 2300
Relay_Log_File: 53-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master52.000001
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: 2300
Relay_Log_Space: 523
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: 52
Master_UUID: 3b85ac97-b635-11e8-b479-5254007aa2ad
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
复制模式介绍
• 异步复制( Asynchronous replication )
– 主库在执行完客户端提交的事务后会立即将结果返给客户端,并不关心从库是否已经接收并处理。
• 全同步复制( Fully synchronous replication )
– 当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。
• 半同步复制( Semisynchronous replication )
– 介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到 relay log 中才返回给客户端
检查是否允许动态载入模块
mysql> show variables like “have_dynamic_loading”;
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| have_dynamic_loading | YES |
+———————-+——-+
1 row in set (0.01 sec)