mysql 分发_MYSQL数据同步、复制、分发

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/f

[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/f

[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

通过位置还原：

–start-position=”还原数据的起始位置”

–stop-position=”还原数据的结束位置”

mysqlbinlog –start-position=“位置” 日志文件路径 | mysql -u用户 -p密码 –从规定的起始位置还原到现在

mysqlbinlog –stop-position=“位置” 日志文件路径 | mysql -u用户 -p密码 –从最开始还原到规定的结束位置

mysqlbinlog –start-position=“位置” –stop-position=“位置” 日志文件路径 | mysql -u用户 -p密码 –指定位置段