版权声明:此文章转载自infocool
如需转载请联系听云College团队成员小尹 邮箱:yinhy#tingyun.com
MYSQL主从复制:
1,主从服务器都启用二进制日志,设置唯一ID,并相互建立连接的账号并授权;
2,从服务器主动向主服务器请求查询当前同步状态,并拉回数据;
主要相关命令:
查看主服务器状态:show master status\G;
查看从服务器状态:show slave status\G;
查看mysql I/O线程:show processlist\G;
配置从服务器复制:change master to {***}详见后面说明
启动从服务器复制:start slave;
授权用户:grant 权限 on 数据库.* to 用户名@'登录主机' identified by "密码";
刷新系统权限表:flush privileges;
锁定数据库:flush tables with read lock;
解除锁定库:unlock tables;
相关设置
主服务器:
#vi /etc/my.cnf [mysqld] log-bin=mysql-bin //[必须]启用二进制日志 server-id=222 //[必须]服务器唯一ID,默认是1,一般取IP最后一段 binlog-do-db=wordpress //表示只备份wordpress binlog_ignore_db=mysql //表示忽略备份mysql 不加binlog-do-db和binlog_ignore_db,那就表示备份全部数据库。 mysql>GRANT REPLICATION SLAVE ON *.* to 'copy_user'@'%' identified by 'copy_password'; --这里要注意:REPLICATION SLAVE,指主服务器复制权限 mysql>show processlist; --查看已连接的从服务器
从服务器:
#vi /etc/my.cnf [mysqld] log-bin=mysql-bin //[不是必须] 启用二进制日志 ,当从服务器是其他服务器的主服务器时,必须要设置 server-id=111 //[必须] 服务器唯一ID,默认是1,一般取IP最后一段 slave-skip-errors = 1062 //[不是必须]忽略重复键值错误 replicate-do-db=test replicate-ignore-db=mysql mysql>GRANT REPLICATION CLIENT ON *.* to 'copy_user'@'%' identified by 'copy_password'; --这里要注意:REPLICATION CLIENT指从服务器复制权限 mysql>change master to --配置连接到哪个主服务器 ->master_host='192.168.2.12', --主服务器IP地址 ->master_user='copy_user', --主服务器中配置的用户名 ->master_password='copy_password', --主服务器中的密码 ->master_log_file='mysql-bin.000039', --就是当前主服务器日志文件名,就是从这个文件开始复制,不大于前边的File值。 ->master_log_pos=32176; --从上面日志文件中的相应位置开始,前边的Position值 , mysql>start slave; --启动从服务器复制功能] mysql>stop slave; mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; --忽略1步错误
mysql>stop slave; mysql>change master to master_host='192.168.1.21',master_user='copy_user', master_password='copy_password',master_log_file='mysql-bin.000031', master_log_pos=108506; mysql>start slave;
查看主服务器状态,在主服务器上运行:
mysql> show master status\G; *************************** 1. row *************************** File: mysql-bin.000038 Position: 95990 //当前日志位置 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) ERROR: No query specified
查看从服务器当前复制状态,在从服务器上运行:
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.12 //主服务器地址 Master_User: copy_king //授权帐户名 Master_Port: 3306 //数据库端口 Connect_Retry: 60 //当连接错误,间隔多长时间重试 Master_Log_File: mysql-bin.000038 //当前主服务器的最大日志ID Read_Master_Log_Pos: 95990 //当前主服务器进行到的日志位置,这与主服务器中的值相同 Relay_Log_File: kq126-relay-bin.000053 //自己的日志位置 Relay_Log_Pos: 96153 //同步读取二进制日志的位置,大于等于Exec_Master_Log_Pos Relay_Master_Log_File: mysql-bin.000038 //已同步到主服务器的位置 Slave_IO_Running: Yes //此状态必须YES,连接到主库是否正在运行 Slave_SQL_Running: Yes //此状态必须YES, 本库内写入状态是否运行,这两个都必须是YES,只要有一个是NO,就是出问题了 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: 95990 // Relay_Log_Space: 96489 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 从服务器数据比主服务器延迟多少秒,一般是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: 9 Master_UUID: 7d911e39-3a98-11e5-8b9e-000c29168ea6 Master_Info_File: /usr/local/mysql/var/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) ERROR: No query specified
查看日志,在linux中:
/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/var/mysql-bin.000050
通过日志还原:
通过时间还原:
–start-datetime=”还原数据的起始时间”
–stop-datetime=”还原数据的结束时间”
mysqlbinlog –start-datetime=“时间” 日志文件路径 | mysql -u用户 -p密码 –从规定的起始时间还原到现在
mysqlbinlog –stop-datetime=“时间” 日志文件路径 | mysql -u用户 -p密码 –从最开始还原到规定的结束时间
mysqlbinlog –start-datetime=“时间” –stop-datetime=“时间” 日志文件路径 | mysql -u用户 -p密码 –指定时间段
[root@localhost bin]# /usr/local/mysql/bin/mysqlbinlog --stop-datetime='2015-10-19 17:30:0' /usr/local/mysql/var/mysql-bin.000001 | mysql -uroot -proot
1
通过位置还原:
–start-position=”还原数据的起始位置”
–stop-position=”还原数据的结束位置”
mysqlbinlog –start-position=“位置” 日志文件路径 | mysql -u用户 -p密码 –从规定的起始位置还原到现在
mysqlbinlog –stop-position=“位置” 日志文件路径 | mysql -u用户 -p密码 –从最开始还原到规定的结束位置
mysqlbinlog –start-position=“位置” –stop-position=“位置” 日志文件路径 | mysql -u用户 -p密码 –指定位置段