mysql本地同步服务器时间设置_mysql服务器主从同步设置

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';