mysql-replication

Auth: jin

Date: 20140422

http://dev.mysql.com/doc/refman/5.1/zh/replication.html#replication-options
(一)两个全新的实例做master-slave
1.master配置
[root@master1 ~]# cat /etc/my.cnf
[mysqld]
datadir = /data/mysql/3306
port = 3306
socket = /data/mysql/3306/mysql.sock
pid-file = /data/mysql/3306/mysql.pid

#log
log-error = /data/logs/mysql/3306_error.log
long_query_time = 2
slow-query-log-file = /data/logs/mysql/3306_slow.log

log-bin = binlogs/mysql-bin
binlog_format = ROW
expire_logs_day =1 #过期时间 超过手动清除
max_binlog_size =500M #每个文件大小
binlog_cache_size=3M

2.master初始化并启动
mysql_install_db --datadir=/data/mysql/3306 --user=mysql

3.master赋予复制权限
mysql> grant REPLICATION SLAVE on *.* to ‘repl‘@‘192.168.11.%‘ identified by ‘replpwd‘;

4.slave初始化并启动
slave不用开启binlog(忘记开不开了，郁闷)

5.同步
1)获取master position
mysql -S /data/mysql/mysql.sock --password=password -e"show master status;"
2)slave上设置同步位置
change master to MASTER_HOST=‘192.168.11.100‘,MASTER_PORT=3306,MASTER_USER=‘repl‘,MASTER_PASSWORD=‘replpwd‘,MASTER_LOG_FILE=‘mysql-bin.000004‘,MASTER_LOG_POS=106;
3)启动slave
mysql> start slave;
4)查看slave状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event #SHOW PROCESSLIST输出的State字段的拷贝 show processlist看到两个system user线程，一个是IO,一个是SQL线程
#388 | dbslave | 192.168.201.2:44540 | NULL | Binlog Dump | 23567257 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL #这个master push binlog到slave的线程
Master_Host: 192.168.11.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004 #I/O线程当前正在读取的主服务器二进制日志文件的名称。
Read_Master_Log_Pos: 106 #在当前的主服务器二进制日志中，I/O线程已经读取的位置。
Relay_Log_File: mysql-relay-bin.000002 #SQL线程当前正在读取和执行的中继日志文件的名称。
Relay_Log_Pos: 251 #在当前的中继日志中，SQL线程已读取和执行的位置
Relay_Master_Log_File: mysql-bin.000004 #由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称。
Slave_IO_Running: Yes ###I/O线程是否被启动并成功地连接到主服务器上，从master读取binlog写到slave的relay-binlog文件中
Slave_SQL_Running: Yes ###SQL线程状态，从slave的relay-binlog文件解析成sql语句应用到slave数据库
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,test,performance_schema #忽略复制的库
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0 #被多数最近被执行的查询返回的错误数量
Last_Error: #错误消息。
Skip_Counter: 0 #最近被使用的用于SQL_SLAVE_SKIP_COUNTER的值
Exec_Master_Log_Pos: 106 #来自master服务器的二进制日志的由SQL线程执行的上一个时间的位置（Relay_Master_Log_File）
在主服务器的二进制日志中的(Relay_Master_Log_File,Exec_Master_Log_Pos)对应于在中继日志中的(Relay_Log_File,Relay_Log_Pos)[好像不是,好像对应Master_Log_File,Read_Master_Log_Pos]。
Relay_Log_Space: 406 #所有原有的中继日志结合起来的总大小。
Until_Condition: None #在START SLAVE语句的UNTIL子句中指定的值
Until_Condition具有以下值：
如果没有指定UNTIL子句，则没有值
如果从属服务器正在读取，直到达到主服务器的二进制日志的给定位置为止，则值为Master
如果从属服务器正在读取，直到达到其中继日志的给定位置为止，则值为Relay
Until_Log_File和Until_Log_Pos用于指示日志文件名和位置值。日志文件名和位置值定义了SQL线程在哪个点中止执行。
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
本字段是从属服务器“落后”多少的一个指示。当从属SQL线程正在运行时（处理更新），本字段为在主服务器上由此线程执行的最近的一个事件的时间标记开始，已经过的秒数。
当此线程被从属服务器I/O线程赶上，并进入闲置状态，等待来自I/O线程的更多的事件时，本字段为零。总之，本字段测量从属服务器SQL线程和从属服务器I/O线程之间的时间差距，
单位以秒计。
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
http://blog.csdn.net/shiqidide/article/details/7263652

(二)已有的实例 扩展成master-slave
在线操作
1.master授权slave复制和dump
复制
grant REPLICATION SLAVE on *.* to ‘repl‘@‘192.168.100.%‘ identified by ‘replpwd‘;
dump
grant all on dbtest.* to ‘dbslave‘@‘192.168.100.%‘ identified by ‘dbslavepwd‘;
grant all on dbtest2.* to ‘dbslave‘@‘192.168.100.%‘ identified by ‘dbslavepwd‘;

2.master开启binlog
1)配置打开binlog
#vim /etc/my.cnf
log-bin=mysql-bin
binlog_format = ROW
#binlog_format=mixed
expire_logs_day=1
max_binlog_size=500M
binlog_cache_size=3M
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = performance_schema
replicate-ignore-db = information_schema
server-id = 33061

注意server-id定义
2)重启服务生效
/etc/init.d/mysql restart
mysql> SHOW PLUGINS;
+------------+--------+----------------+---------+---------+
| Name | Status | Type | Library | License |
+------------+--------+----------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |

3.准备slave服务器
slave不用开启binlog
1）测试赋值账号连接
mysql -h192.168.100.10 -P3306 -urepl -preplpwd
2)配置server-id
server-id = 33062
要比master大
3）配置复制账号
mysql> stop slave;
mysql> change master to MASTER_HOST=‘192.168.100.10‘,MASTER_PORT=3306,MASTER_USER=‘repl‘,MASTER_PASSWORD=‘replpwd‘
先配置好账号密码，不然后面的file和position信息无法写入
确认配置
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.100.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: 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: 0
Relay_Log_Space: 106
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

4.在线直接从master dump到slave,并启动slave
1)测试dump账号
mysql -h192.168.100.10 -udbslave -pdbslavepwd -P3306 dbtest
2)导入
$ mysqldump -h192.168.100.10 -udbslave -pdbslavepwd -P3306 --master-data --databases dbtest dbtest2|mysql -u root -p -h 127.0.0.1 -P 3306
dump默认-opt参数会锁库锁表，保证dump点数据一致性
$ mysql -u root -p -h 127.0.0.1 -P 3306
确认一下配置
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.100.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002 #增加部分
Read_Master_Log_Pos: 237 #增加部分
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002 #增加部分
Slave_IO_Running: No
Slave_SQL_Running: 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: 237 #增加部分
Relay_Log_Space: 106
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
增加部分
Master_Log_File: mysql-bin.000002 #增加部分
Read_Master_Log_Pos: 237 #增加部分
Relay_Master_Log_File: mysql-bin.000002 #增加部分
Exec_Master_Log_Pos: 237 #增加部分
这四个信息和master的 file，position有关了

3)启动slave
start slave
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4)测试
master
insert into t2(name) values (‘bing‘);
slave
mysql> select * from t2 where name=‘bing‘;
+----+------+
| id | name |
+----+------+
| 3 | bing |
+----+------+
1 row in set (0.00 sec)

5. --master-data补充说明
在做主从dump数据加上--master-data，也就是--master-data=1
mysqldump出来的文件就会包括CHANGE MASTER TO这个语句，CHANGE MASTER TO后面紧接着就是file和position的记录，
file和position记录的位置就是slave从master端复制文件的起始位置。CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000084‘, MASTER_LOG_POS=725240484
然后start slave即可

(三)双主-互为主从
http://www.cnblogs.com/diege/p/3355875.html

(四)清空slave配置，重新同步
1）
mysql> stop slave;
状态
Slave_IO_Running: No
Slave_SQL_Running: No
mysql> reset slave;
状态
Master_Log_File: 没有了
Read_Master_Log_Pos: 4 #从master位置变成slave位置了
Relay_Log_File: mysqld-relay-bin.000001 #从master的变成slave的文件里
Relay_Log_Pos: 4
Relay_Master_Log_File:
Exec_Master_Log_Pos: 0 #变0了

2.清空后可以重新配置
master
全局只读锁
mysql> FLUSH TABLES WITH READ LOCK;
获取master信息
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 332 | | |
+------------------+----------+--------------+------------------+
slave 重新同步
change master to MASTER_LOG_FILE=‘mysql-bin.000003‘,MASTER_LOG_POS=332;
mysql> start slave;
确认
mysql> show slave status\G
master解锁
mysql> unlock tables;

3.清理binlog
mysql> SHOW master LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1469 |
| mysql-bin.000002 | 256 |
| mysql-bin.000003 | 332 |
+------------------+-----------+
3 rows in set (0.00 sec)

2.手动清理日志
1)指定时间
PURGE {MASTER | BINARY} LOGS BEFORE ‘date’ #清除多少日期前的日志
例如：
PURGE MASTER LOGS BEFORE ‘2008-06-22 13:00:00‘;
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);#现在的时间为基准，往前三天的前的日志清
2）全部清空
reset后 File和Position都重新计算
mysql> reset master;
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW master LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 106 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> SHOW master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql-replication,布布扣,bubuko.com

mysql-replication

原文：http://www.cnblogs.com/diege/p/3681980.html