MYSQL：主从服务器同步例子(修改版)

mysql的cnf中不区分 - 和 _ ，也可以交替使用，如：binlog_do-db


1、master配置
1.1 /etc/my.cnf 中加入
...
server-id=1
log-bin=mysql-bin
binlog-do-db=book book为数据库名
binlog-do-db=book2
...
或者用排除法设置同步库:
binlog-ignore-db=test
...
授权给rep用户进行复制操作
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON book.* TO rep@192.168.1.50 IDENTIFIED BY '123456';
flush privileges;
在slave上测试该帐号能否登录master。
1.2 重启master服务
1.3 备份出这个节点文件(--master-data参数会生成change master to。。。)
mysqldump -urep -p --master-data --lock-all-tables -E -F --all-databases >/tmp/all-databases-full-bak.sql

或者手工tar master上的/var/lib/mysql数据目录到slave：
mysql>flush tables with read lock;  清空所有表的写操作。
mysql>show master status; 查看master-log-file/position
保持mysql client窗口不关，
cd /var/lib/mysql
tar czvf /tmp/mysql-all-full-bak.tar.gz .
mysql>unlock;
然后再复制解压到slave的相应目录下。



2、配置slave
2.1 vi /etc/my.cnf
设置如下面
server-id       = 2
master-host     =   192.168.1.51
master-port     =   3306
master-user     =   rep
master-password =   123456
master-connect-retry = 60
read_only = 1 #slave只读
...
2.2重启slave并登录mysql,slave stop;先停止复制。
2.3 导入mysqldump备份的数据。由于mysqldump备份时使用了--master-data(默认值为1),所以包含了
channge master-log-file文件和position位置，直接导入即可。
2.4
mysql>slave start;
mysql>show slave status\G;

3.检查排错。
注释：为了保证事务InnoDB复制设置的最大可能的耐受性和一致性，应在主服务器的my.cnf文件中使用innodb_flush_log_at_trx_commit=1和sync-binlog=1。

----------------------------------------------------------------------

提示：如果修改了主服务器的配置，记得删除从服务器上数据库目录下的 master.info文件。否则从服务器使用的还是老配置，可能会导致错误。

----------------------------------------------------------------------

补充：

在从服务器上使用show slave status\G
Slave_IO_Running, 为No,
则说明IO_THREAD没有启动，请执行start slave io_thread
Slave_SQL_Running 为No
则复制出错,查看Last_error字段排除错误后执行start slave sql_thread

查看Slave_IO_State字段空 //复制没有启动
Connecting to master//没有连接上master
Waiting for master to send event//已经连上

补充：可以使用LOAD DATA FROM MASTER语句来建立slave。但有约束条件：
数据表要全部是MyISAM表，必须有SUPER权限，master的复制用户必须具备 RELOAD和SUPER权限。
在master端执行RESET MASTER清除已有的日志变更，
此时slave端会因为找不到 master日志无法启动IO_THREAD，请清空data目录下
relay-log.info,hosname-relay-bin*等文件 重新启动mysql
中继日志文件默认的文件为hostname-relay-bin.nnn和hostname-relay- bin.index。可用从服务器的--
relay-log和--relay-log-index选项修改。在从服务器中还有一个relay- log.info中继信息文件，可用
--relay-log-info-file启动选项修改文件名。
双机互备则是两个mysql同时配 置为master及slave

主服务器上的相关命令：
show master status
show slave hosts
show logs
show binlog events
purge logs to 'log_name'
purge logs before 'date'
reset master(老版本flush master)
set sql_log_bin=

从服务器上的相关命令:
slave start
slave stop
SLAVE STOP IO_THREAD //此线程把master段的日志写到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此线程把写到本地的日志应用于数据库
SLAVE start SQL_THREAD
reset slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //动态改变master信息
PURGE MASTER [before 'date'] 删除master端已同步过的日志

-----------------------------------------------------------

字段名	注释	取值示例
Slave_IO_State	slave状态的文字描述	Waiting for master to send event
Master_Host	master的IP或主机名	10.0.0.1
Master_User	连接master使用的用户名	raaa
Master_Port	连接master使用的密码	3306
Connect_Retry	重新连接MySQL的重试间隔时间，单位秒	60
Master_Log_File	IO thread读取到的binlog日志文件	mysql-bin.0018
Read_Master_Log_Pos	IO thread读取到的binlog日志文件位置	6029100
Relay_Log_File	slave 在本地缓存的relay 日志的文件名	mysql-relay-bin.0014
Relay_Log_Pos	slave 在本地缓存的relay 日志的文件位置	74125
Relay_Master_Log_File	SQL thread 执行到master的binlog文件名	mysql-bin.0018
Slave_IO_Running	IO thread是否正常运行	Yes
Slave_SQL_Running	SQL thread是否正常运行	Yes
Replicate_Do_DB	slave上需要执行的schema
Replicate_Ignore_DB	slave上需要忽略的schema
Replicate_Do_Table	slave上需要执行的table
Replicate_Ignore_Table	slave上需要忽略的table
Replicate_Wild_Do_Table	slave上需要执行的table正则表达式
Replicate_Wild_Ignore_Table	slave上需要忽略的table正则表达式
Last_Errno	上一次出错的错误号	0
Last_Error	上一次出错的错误信息
Skip_Counter	还剩下的忽略event次数	0
Exec_Master_Log_Pos	SQL thread 执行到master的binlog文件位置	602100
Relay_Log_Space	relay log占用的空间大小	741723
Until_Condition	复制until条件，在stop slave,start slave(不带until)或server重启的时候会自动重置	None
Until_Log_File	复制停止的文件名
Until_Log_Pos	复制停止的文件位置	0
Master_SSL_Allowed	是否使用SSL连接master	No
Master_SSL_CA_File	ssl agent文件ca-cert.pem的文件名
Master_SSL_CA_Path	ssl agent文件ca-cert.pem的路径名
Master_SSL_Cert	ssl 授权文件
Master_SSL_Cipher	ssl 加密算法
Master_SSL_Key	ssl 密钥文件
Seconds_Behind_Master	SQL thread相对master的延迟时间	0
Master_SSL_Verify_Server_Cert	是否检查master的授权文件	No
Last_IO_Errno	IO thread的上一次出错的错误号	0
Last_IO_Error	IO thread的上一次出错的错误信息
Last_SQL_Errno	SQL thread的上一次出错的错误号	0
Last_SQL_Error	SQL thread的上一次出错的错误信息