本节介绍 mysql主从配置。
1、系统环境
主服务器：192.168.8.99
从服务器：192.168.8.100 新建从服务器，用于同步主服务器数据。
2、配置
(1)、主服务器配置192.168.8.99
#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.8.99 -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.8.99' IDENTIFIED BY "123456";
mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.99',
-> 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 statusG;
.......
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';
>>> 更多内容，请访问：mysql主从复制、
mysql主从同步系列教程
