MySQL-5.7.20主从复制测试[20180110]
2018-06-18 00:44:12来源：未知 阅读 ()
前言
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;
参考：
https://segmentfault.com/a/1190000010867488
https://www.jianshu.com/p/208667156faf
标签：
版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有
上一篇：mysql主主复制汇总整理
相关文章
- MySQL 主从复制(实时热备)原理与配置 2019-08-13
- 面试必问！5 分钟带你搞定 Redis 复制（会的举个手） 2019-08-09
- 如何解决mysql主从数据库不同步 2019-08-09
- 如何保障MySQL主从复制关系的稳定性？关键词（新特性、crash 2019-07-24
- Mysql 主从报错：1141 2019-07-24
IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash
热门词条
最新资讯
热门关注
热门标签