Linux 下Mysql的主从复制
master ip:192.168.20.60
slave ip:192.168.20.61
##对master的操作
1.创建专门用于备份的用户:
mysql> grant replication slave on *.* to 'repl'@'192.168.20.61' identified by 'repl';
2.开启binarylog
`vi /etc/my.cnf`
log-bin=mysql-bin
server-id=60
#innodb_flush_log_at_trx_commit = 1
#sync_binlog=1
#binlog-do-db=test
binlog-ignore-db=mysql,information_schema,performance_schema # 忽略的数据库
#replicate-do-db=test
修改mysql配置文件后需重启mysql服务 : `service mysqld restart`
* ※导出现有数据库。如果现有库要同步到 slave ,需要先导出,然后传到 slave 进行导入
# 先锁定
mysql> FLUSH TABLES WITH READ LOCK;
# 导出
mysqldump --master-data --databases -uroot DB1 DB2 ... > data.sql
# 这里可以接着执行下面的步骤3
mysql> show master status\G
# 解锁
mysql> UNLOCK TABLES;
# 传送
scp data.sql root@192.168.20.61:~
# slave 端导入
mysql -uroot < ~/data.sql
3.查看并记下master状态
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 1406
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema
1 row in set (0.00 sec)
# 记住两个状态:
# 日志文件 mysql-bin.000005
# 记录的位置 1406
## slave 操作
1.开启binarylog (同 master 操作, 修改 `server-id=61` 即可)
2.配置 slave
mysql> CHANGE MASTER to
MASTER_HOST='192.168.20.60',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=1406;
# 根据 master 状态指定相应的值
3.启动 slave
mysql> start slave;
4.查看 slave 状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.20.60
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 2281
Relay_Log_File: test-61-relay-bin.000002
Relay_Log_Pos: 1141
Relay_Master_Log_File: mysql-bin.000005
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: 2281
Relay_Log_Space: 1312
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: 60
Master_UUID:
Master_Info_File: /usr/local/mysql/data/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)
# 有这两行说明开启成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# 不成功可以查看错误日志,检查状态是否准确,MySQL 端口是否放行,MySQL 配置是否生效等等。
----------------------------------------------------------------
到这里,简单的主从复制配置完成,实现的效果是 **除了指定的不同步的数据库外,master 上的数据库变化都将同步到 slave**。
要说明的是同步的效果是单向的,slave 上的修改不会同步到 master 上。
----------------------------------------------------------------
参考
http://www.cnblogs.com/shuidao/p/3551238.html
http://369369.blog.51cto.com/319630/790921
http://laoxu.blog.51cto.com/4120547/1198728