Day11.Mysql主从配置

1、配置主-从-从结构,并做各种测试
第一步,配置主库51
vim /etc/my.cnf

[[email protected] 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”

[[email protected] mysql]# systemctl restart mysqld
[[email protected] 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 [email protected]”192.168.4.50″ identified by “123456”;
Query OK, 0 rows affected, 1 warning (0.08 sec)

第二步,配置从库52
[[email protected] mysql]# vim /etc/my.cnf
[[email protected] 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

[[email protected] mysql]# systemctl restart mysqld
[[email protected] mysql]# mysql -uroot -p123456

mysql> grant replication slave on *.* to [email protected]”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
[[email protected] mysql]# vim /etc/my.cnf
[[email protected] 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

[[email protected] mysql]# systemctl restart mysqld
[[email protected] 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 [email protected]”192.168.4.50″ identified by “123456”;
客户端50远程登陆53查看
[[email protected] 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上面去看一眼,库里面没有表
[[email protected] 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报错了
[[email protected] 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看一眼,是正常的
[[email protected] 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自己的表,没有变化
[[email protected] 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不会同步
[[email protected] 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
[[email protected] 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)

此条目发表在mysql分类目录,贴了标签。将固定链接加入收藏夹。

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注