1、说明
主服务器：192.168.0.17
从服务器：192.168.0.68 新建的一个从服务器，需要同步主服务器数据。
2、配置
(1)、主服务器配置192.168.0.17
#vim /etc/my.cnf 加入以下几行。
server-id = 1105161846
log-bin=mysql-bin
log_bin_index = mysql-bin.index
binlog_do_db = test_db
sync_binlog = 1
重启mysql服务
#/etc/init.d/mysqld restart
#mysql -uroot -p
mysql>show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000038 | 32430008 | test_db | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
创建复制账号
mysql>GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.0.68' IDENTIFIED BY "123456";
mysql>flush privileges;
(2)、从服务器配置192.168.0.68
#vim /etc/my.cnf 加入以下几行。
log-bin=mysql-bin
log_bin_index = mysql-bin.index
log_slave_updates
relay_log = mysql-relay-bin
relay_log_index = mysql-relay-bin.index
max_binlog_size = 200M
slave-skip-errors = 1062,1053
skip_slave_start = 1
expire_logs_days = 7
sync_binlog = 100
把主数据库的db导入从数据库
#mysqldump -h192.168.0.17 -u test_db -p ‘123456’ -R --triggers --single-transaction --flush-logs --default-character-set=utf8 --master-data --database test_db > test_db.sql
#mysql -uroot -p -e "source < ~/test_db.sql"
设置同步
#mysql -uroot -p
mysql>GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.0.17' IDENTIFIED BY "123456";
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.17',
-> MASTER_PORT=3306,
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000038',
-> MASTER_LOG_POS=32430008;
mysql>slave start;
mysql>show slave status\G;
.......
Master_Log_File: mysql-bin.000038
Read_Master_Log_Pos: 37762194
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 25417693
Relay_Master_Log_File: mysql-bin.000038
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
........
表示同步成功！
清除binlog
mysql>PURGE MASTER LOGS TO 'mysql-bin.000243';
本文出自 “linux运维” 博客，请务必保留此出处http://linux008.blog.51cto.com/2837805/595660