Linux环境下MySQL5.6 Master-Slave的搭建步骤
发布时间：2022-01-19 13:33:43 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要讲解了Linux环境下MySQL5.6 Master-Slave的搭建步骤，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习Linux环境下MySQL5.6 Master-Slave的搭建步骤吧！ Master-Slave搭建步骤： 1、在两台主机分
|
这篇文章主要讲解了“Linux环境下MySQL5.6 Master-Slave的搭建步骤”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Linux环境下MySQL5.6 Master-Slave的搭建步骤”吧！ Master-Slave搭建步骤： 1、在两台主机分别安装MySQL数据库软件 2、在两台主机分别创建并初始化MySQL数据库实例 3、从主库dump数据(库)导入到备库 4、修改主从(Master-Slave)配置 5、主库grant、从库启动复制(start slave) 6、验证主备数据同步 --master: grant replication slave,replication client on *.* to 'slave'@'192.168.100.81' identified by 'slave'; 导出： flush tables with read lock; mysql> insert into tony(tid,tdept) values (20001,'ty'); ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock mysqldump -uroot -p -E -R -A --databases tyms> /mysql/data/tyms_master.sql  slave导入: mysql命令行 mysql>source /mysql/data/tyms_master.sql; --master: (root@localhost) [(none)]> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000014 | 594 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ---slave: change master to master_host='192.168.100.71', master_user='slave', master_password='slave', master_log_file='mysql-bin.000014', master_log_pos=594; (root@localhost) [(none)]> change master to master_host='192.168.100.71', master_user='slave', master_password='slave', master_log_file='mysql-bin.000014', master_log_pos=594; Query OK, 0 rows affected, 2 warnings (0.02 sec) --master: unlock tables; ---slave: mysql> start slave; [tyms]> show slave status G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.71 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000014 Read_Master_Log_Pos: 3051261 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 3050950 Relay_Master_Log_File: mysql-bin.000014 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: 3051261 Relay_Log_Space: 3051123 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: 1000 Master_UUID: 845f69fa-c558-11e5-9462-000c297b003d Master_Info_File: /mysql/data/mysqldata/mydata/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 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 1 row in set (0.00 sec) [tyms]> show binlog events in '/mysql/data/mysqldata/mydata/mysql-bin.000014' from 3051261; [(none)]> select version(); +------------+ | version() | +------------+ | 5.6.28-log | +------------+ 1 row in set (0.00 sec) 可以看到：Slave_IO_Running | Slave_SQL_Running两个值都是YES，说明配置成功了。可以在主库里执行DML或者DDL验证下。 如果同步不成功： 1：停掉从库 mysql> slave stop 2：主库上找到日志和位置 mysql> show master status; (root@localhost) [(none)]> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000014 | 594 | | | | +------------------+----------+--------------+------------------+-------------------+ 3：手动同步 mysql> change master to > master_host='master_ip', > master_user='gechong', > master_password='gechong', > master_port=3306, > master_log_file='mysql-bin.000014', > master_log_pos=594; 1 row in set (0.00 sec) 4：启动从库 mysql> slave start; 1 row in set (0.00 sec) 如果有异常需要可执行跳过： >slave stop; >SET GLOBAL sql_slave_skip_counter = 1; >slave start; Created by Tony.Tang[TangYun]2016.02 －－－－－－－－－－－－－－－－－－－－－－End－－－－－－－－－－－－－－－－－－－－－－－－－－ 感谢各位的阅读，以上就是“Linux环境下MySQL5.6 Master-Slave的搭建步骤”的内容了，经过本文的学习后，相信大家对Linux环境下MySQL5.6 Master-Slave的搭建步骤这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。 （编辑：温州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
