1、一主一从分配 环境:CentOS7 + Percona-Server-server-57-5.7.32 MySQL安装请参考博文:CentOS7下MySQL(Percona-Server-5.7)安装及简单使用 。 主:192.168.178.130 从:192.168.178.150
2、主数据库master配置 1.修改mysql配置 找到主数据库的配置文件my.cnf(或者my.ini),我的在/etc/percona-server.conf.d/mysqld.cnf下,相关配置项如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 # 开启二进制日志 log_bin=mysql-bin binlog_format=ROW # 设置server-id server-id=130 # 同步的数据库 binlog-do-db=test # 不同步哪些数据库 binlog-ignore-db = mysql binlog-ignore-db = information_schema datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 log-error=/var/log/mysqld.log
修改完后需要重启mysql:
2.创建用于同步的用户账号 打开mysql会话:
创建用户并授权:
用户:repl 密码:123456
1 2 3 mysql> CREATE USER 'repl' @'localhost' IDENTIFIED BY '123456' ; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl' @'localhost' ; mysql> flush privileges ;
3.查看master状态 1 mysql> show master status ;
记录二进制文件名(mysql-bin.000001)和位置(763)。
3、从服务器slave配置 1.修改mysql配置 同样找到my.cnf配置文件,server-id=150,最终配置如下:
1 2 3 4 5 6 7 8 9 10 11 12 [mysqld] log_bin =mysql-bin binlog_format =ROW server-id =150 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
修改配置后重启mysql。
2.执行同步SQL语句 打开mysql会话,执行同步SQL语句(需要主服务器主机名,登陆账号,二进制文件的名称和位置):
1 mysql> CHANGE MASTER TO MASTER_HOST='192.168.178.130',MASTER_USER='repl',MASTER_PASSWORD='123456 ',MASTER_LOG_FILE='mysql-bin.000001 ',MASTER_LOG_POS=763 ;
3.启动slave同步进程:
4.查看slave状态 mysql> show slave status\G;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 mysql> show slave status\G; *************************** 1 . row *************************** Slave_IO_State: Master_Host: 192.168 .178 .130 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 154 Relay_Log_File: 192 -relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: No 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: 154 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: 1593 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 130 Master_UUID: 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: 210102 23 :03:33 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)
检查从库是否同步成功:
‘Slave_IO_Running: Yes’ (负责与主库同步IO通信)
‘Slave_SQL_Running: Yes’ (负责自身slave mysql进程)
上面有一处异常:
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
原因分析:
mysql 5.7的复制引入了uuid的概念,各个复制结构中的server_uuid得保证不一样,但是查看到直接copy data文件夹后server_uuid是相同的 mysql> show variables like ‘%server_uuid%’;
解决方法:
找到data文件夹下的auto.cnf文件,修改里面的uuid值,保证各个db的uuid不一样,重启mysql即可。
再次查看slave状态:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 mysql> show slave status\G; *************************** 1 . row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168 .178 .130 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 154 Relay_Log_File: 192 -relay-bin.000003 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000004 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: 525 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: 130 Master_UUID: fb6c6f2b-4ca3-11eb-9e30-000c293fa755 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.02 sec)
4、主从同步测试 登录主mysql(192.168.178.130), 创建数据库test:
1 mysql> create database test;
登录从mysql(192.168.178.150),查看数据库:
在主mysql上test数据库中新建表tb_user;
1 2 3 4 5 6 7 CREATE TABLE `tb_user` ( `id` int (11 ) NOT NULL AUTO_INCREMENT, `user_name` varchar (20 ) DEFAULT '' , `passwd` varchar (20 ) DEFAULT NULL , `age` smallint (6 ) DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE= InnoDB DEFAULT CHARSET= utf8mb4;
查看从mysql test数据库下的表:
在主mysql上tb_user上新增一条数据:
1 INSERT INTO tb_user (id, user_name, passwd, age) VALUES (1 , 'xiaowang' , '123456' , 18 );
在从数据库上查看:
会看到主数据库上的操作都同步到了从数据库上。
按照目前的配置,从数据库也是可以写入数据的,但从的写入不会同步到主,会导致主从数据不一致;
从库上限制用户写入数据,在从服务中将read_only参数的值设为1:
1 mysql> set global read_only =1;
生产上建议在主库上写数据,从库上读数据,这样就可以做到读写分离。