MySQL-5.7.20主从复制测试[20180110]
前言
MySQL 5.7.20测试主从复制
环境
主库 192.168.1.59 t-xi-sonar01
从库 192.168.1.51 t-xi-orc01
设定主机host文件
主库 [root@t-xi-sonar01 ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.1.59 t-xi-sonar01 192.168.1.51 t-xi-orc01
从库 [root@t-xi-orc01 ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.1.51 t-xi-orc01 192.168.1.59 t-xi-sonar01
Mysql数据库配置
【Master-Server】
[root@t-xi-sonar01 ~]# service mysqld stop Stopping mysqld: [ OK ] [root@t-xi-sonar01 ~]# vim /etc/my.cnf #Server ID，一般设置成IP地址的最后一位，如下测试就按后两位 server_id=59 #开启log bin，名字最好有意义用来区分 log-bin=dev-bin #需要进行复制的数据库，可以指定数据库 #binlog-do-db=DB_master #不需要备份的数据库，可以设置多个数据库，一般不会同步mysql这个库 binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schema #为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存 binlog_cache_size=1m #二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。 expire_logs_days=7 # 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。 # 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致 slave_skip_errors=1062 [root@t-xi-sonar01 ~]# service mysqld start Starting mysqld: [ OK ] [root@t-xi-sonar01 ~]# mysql 5.7.20登陆报错解决
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
mysqld_safe --user=mysql --skip-grant-tables --skip-networking & mysql -u root update user set authentication_string=PASSWORD("****") where User='root'; flush privileges; 主库创建同步账户 service mysqld start mysql> mysql -u root -p mysql> CREATE USER 'replication'@'192.168.1.51' IDENTIFIED BY 'slave'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.51'; mysql> flush privileges; 主库锁定后备份将资料同步到从库 mysql>use sonar mysql>FLUSH TABLES WITH READ LOCK; mysqldump -u root -p --databases sonar > sonar.sql scp sonar.sql @192.168.1.51:/root mysql> unlock tables; [master-server] mysql> show master status ; +----------------+----------+--------------+---------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------+----------+--------------+---------------------------------------------+-------------------+ | dev-bin.000004 | 783 | | mysql,information_schema,performance_schema | | +----------------+----------+--------------+---------------------------------------------+-------------------+
【Slave-Server】
service mysqld stop vim /etc/my.cnf #add slave-server server_id=51 #binlog-ignore-db=mydql #binlog-ignore-db=information_schema #binlog-ignore-db=performance_schema #log-bin=dev-slave-bin binlog_cache_size=1M binlog_format=mixed expire_logs_days=7 slave_skip_errors=1062 relay_log=dev-relay-bin #log_slave_updates=1 read_only=1 service mysqld start 将主库备份导入从库 mysql>source /root/sonar.sql 添加链接到主库同步复制的账户 mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.59', MASTER_USER='replication', MASTER_PASSWORD='slave', MASTER_LOG_FILE='dev-bin.000001', MASTER_LOG_POS=0; MASTER_LOG_FILE：指定log bin日志文件名称 MASTER_LOG_POS ：指定同步复制log分区号，可以从0开始。 查看slave状态 show slave status \G Slave_IO_State #从站的当前状态 Slave_IO_Running： Yes #读取主程序二进制日志的I/O线程是否正在运行 Slave_SQL_Running： Yes #执行读取主服务器中二进制日志事件的SQL线程是否正在运行。与I/O线程一样 Seconds_Behind_Master #是否为0，0就是已经同步了 启动slave start slave;
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.59 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: dev-bin.000004 Read_Master_Log_Pos: 1255 Relay_Log_File: dev-relay-bin.000005 Relay_Log_Pos: 1464 Relay_Master_Log_File: dev-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: 1255 Relay_Log_Space: 1878 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: 59 Master_UUID: d6901902-ea28-11e7-b859-000c29255261 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)
【Master-Server】
创建table和database测试 在sonar下建立测试表 mysql> use sonar; mysql> create table slave_t( -> id int(10) not null, name varchar(20) -> ) -> ; Query OK, 0 rows affected (1.57 sec) mysql> insert into slave_t values(1,'name01'); Query OK, 1 row affected (0.33 sec) 创建slave_db测试数据库 mysql> use mysql 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 mysql> create database slave_db; Query OK, 1 row affected (0.17 sec)
【Slave-Server】
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | slave_db | | sonar | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> use sonar; Database changed mysql> select * from slave_t; +----+--------+ | id | name | +----+--------+ | 1 | name01 | +----+--------+ 1 row in set (0.00 sec)
主库上的table和database已同步复制过来
【命令参考】
查看主库master状态 mysql> show master status; +----------------+----------+--------------+---------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------+----------+--------------+---------------------------------------------+-------------------+ | dev-bin.000004 | 1426 | | mysql,information_schema,performance_schema | | +----------------+----------+--------------+---------------------------------------------+-------------------+ 1 row in set (0.00 sec) 查看从库主机列表 mysql> show slave hosts; +-----------+------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+--------------------------------------+ | 51 | | 3306 | 59 | 86fff1d0-f62d-11e7-834d-000c29477dac | +-----------+------+------+-----------+--------------------------------------+ 1 row in set (0.00 sec) 查看bin log文件列表 mysql> show binary logs; +----------------+-----------+ | Log_name | File_size | +----------------+-----------+ | dev-bin.000001 | 177 | | dev-bin.000002 | 177 | | dev-bin.000003 | 177 | | dev-bin.000004 | 1426 | +----------------+-----------+ 4 rows in set (0.00 sec) 查看bin log文件的内容 mysql> show binlog events; +----------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +----------------+-----+----------------+-----------+-------------+---------------------------------------+ | dev-bin.000001 | 4 | Format_desc | 59 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 | | dev-bin.000001 | 123 | Previous_gtids | 59 | 154 | | | dev-bin.000001 | 154 | Stop | 59 | 177 | | +----------------+-----+----------------+-----------+-------------+---------------------------------------+ 3 rows in set (0.00 sec) mysql> show binlog events in 'dev-bin.000004'; +----------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +----------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------+ | dev-bin.000004 | 4 | Format_desc | 59 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 | | dev-bin.000004 | 123 | Previous_gtids | 59 | 154 | | | dev-bin.000004 | 154 | Anonymous_Gtid | 59 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | dev-bin.000004 | 219 | Query | 59 | 417 | CREATE USER 'replication'@'192.168.1.51' IDENTIFIED WITH 'mysql_native_password' AS '*51125B3597BEE0FC43E0BCBFEE002EF8641B44CF' | | dev-bin.000004 | 417 | Anonymous_Gtid | 59 | 482 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | dev-bin.000004 | 482 | Query | 59 | 631 | GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.51' | | dev-bin.000004 | 631 | Anonymous_Gtid | 59 | 696 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | dev-bin.000004 | 696 | Query | 59 | 783 | flush privileges | | dev-bin.000004 | 783 | Anonymous_Gtid | 59 | 848 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | dev-bin.000004 | 848 | Query | 59 | 985 | use `sonar`; create table slave_t( id int(10) not null, name varchar(20) ) | | dev-bin.000004 | 985 | Anonymous_Gtid | 59 | 1050 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | dev-bin.000004 | 1050 | Query | 59 | 1123 | BEGIN | | dev-bin.000004 | 1123 | Table_map | 59 | 1177 | table_id: 326 (sonar.slave_t) | | dev-bin.000004 | 1177 | Write_rows | 59 | 1224 | table_id: 326 flags: STMT_END_F | | dev-bin.000004 | 1224 | Xid | 59 | 1255 | COMMIT /* xid=178006 */ | | dev-bin.000004 | 1255 | Anonymous_Gtid | 59 | 1320 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | dev-bin.000004 | 1320 | Query | 59 | 1426 | create database slave_db | +----------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------+ 17 rows in set (0.13 sec) 查看当前数据库线程列表 【master-server】 mysql> SHOW PROCESSLIST \G *************************** 1. row *************************** Id: 199 User: root Host: localhost db: mysql Command: Query Time: 0 State: starting Info: SHOW PROCESSLIST *************************** 2. row *************************** Id: 201 User: replication Host: t-xi-orc01:41452 db: NULL Command: Binlog Dump Time: 1450 State: Master has sent all binlog to slave; waiting for more updates Info: NULL *************************** 3. row *************************** Id: 203 User: sonar Host: localhost:57162 db: sonar Command: Sleep Time: 434 State: Info: NULL *************************** 4. row *************************** Id: 204 User: sonar Host: localhost:57358 db: sonar Command: Sleep Time: 123 State: Info: NULL *************************** 5. row *************************** Id: 205 User: sonar Host: localhost:57524 db: sonar Command: Sleep Time: 2 State: Info: NULL *************************** 6. row *************************** Id: 206 User: sonar Host: localhost:57720 db: sonar Command: Sleep Time: 3 State: Info: NULL 6 rows in set (0.00 sec) 【slave-server】 mysql> SHOW PROCESSLIST \G *************************** 1. row *************************** Id: 4 User: root Host: localhost db: sonar Command: Query Time: 0 State: starting Info: SHOW PROCESSLIST *************************** 2. row *************************** Id: 5 User: system user Host: db: NULL Command: Connect Time: 1445 State: Waiting for master to send event Info: NULL *************************** 3. row *************************** Id: 6 User: system user Host: db: NULL Command: Connect Time: 26717 State: Slave has read all relay log; waiting for more updates Info: NULL 3 rows in set (0.00 sec) 从库启动复制 mysql> START SLAVE; 从库停止复制 mysql> STOP SLAVE;
参考：
