Mysql服务器的主从同步

Mysql服务器的主辅数据同步

例题：将A服务器作为主服务器（Master），B1和B2为辅服务器（Slave），

怎么来将主服务器的数据同步到辅服务器呢，下面我们来看。

Master:

修改配置文件：/etc/my.cnf

[root@localhost ~]# vim /etc/my.cnf

[mysqld]

log-bin=binlog

log-bin-index=binlog.index

sync_binlog=0

server_id = 1

重启mysql：

[root@localhost ~]# /etc/init.d/mysqld restart

停止 MySQL： [确定]

启动 MySQL： [确定]

[root@localhost ~]#

Slave1：

修改配置文件：/etc/my.cnf

[root@localhost ~]# vim /etc/my.cnf

[mysqld]

server_id = 2

relay_log = /var/lib/mysql/mysql-relay-bin

relay_log_index=/var/lib/mysql/mysql-relay-bin.index

重启mysql：

[root@localhost ~]# /etc/init.d/mysqld restart

停止 MySQL： [确定]

启动 MySQL： [确定]

[root@localhost ~]#

Slave2：

修改配置文件：/etc/my.cnf

[root@localhost ~]# vim /etc/my.cnf

[mysqld]

server_id = 3

relay_log = /var/lib/mysql/mysql-relay-bin

relay_log_index=/var/lib/mysql/mysql-relay-bin.index

重启mysql：

[root@localhost ~]# /etc/init.d/mysqld restart

停止 MySQL： [确定]

启动 MySQL： [确定]

[root@localhost ~]#

Master：

[root@localhost ~]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.0.77-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT replication slave ON *.* TO 'ab'@'%' identified by '123';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

Slave1：

[root@localhost ~]# cd /var/lib/mysql/

[root@localhost mysql]# ls

ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock test

[root@localhost mysql]# rm -rf *

[root@localhost mysql]# ls

[root@localhost mysql]# /etc/init.d/mysqld restart

Master：

[root@localhost ~]# mysqldump -A -x > /tmp/full.sql

[root@localhost ~]# scp /tmp/full.sql root@192.168.18.117:/tmp/

The authenticity of host '192.168.18.117 (192.168.18.117)' can't be established.

RSA key fingerprint is 1f:ce:39:33:61:f5:7d:f8:0b:89:c7:d8:06:46:79:1f.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.18.117' (RSA) to the list of known hosts.

root@192.168.18.117's password:

full.sql 100% 1039MB 4.8MB/s 03:35

[root@localhost ~]#

Slave1：

[root@localhost mysql]# mysql < /tmp/full.sql

Master:

mysql> flush tables with read lock;

mysql> show master status;

+---------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+---------------+----------+--------------+------------------+

| binlog.000003 | 365 | | |

+---------------+----------+--------------+------------------+

1 row in set (0.03 sec)

mysql> unlock tables;

Query OK, 0 rows affected (0.03 sec)

Slave1：

mysql> change master to master_host='192.168.18.107', master_port=3306, master_user='ab', master_password='123', master_log_file='binlog.000003',master_log_pos=365;

Query OK, 0 rows affected (0.06 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Slave1：

[root@localhost ~]# mysqldump -A -x > /tmp/mysql.sql

[root@localhost ~]# scp /tmp/mysql.sql root@192.168.18.127:/tmp/

The authenticity of host '192.168.18.127 (192.168.18.127)' can't be established.

RSA key fingerprint is f7:a5:9e:2f:86:57:a5:17:f4:ad:2b:3a:a8:55:0f:76.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.18.127' (RSA) to the list of known hosts.

root@192.168.18.127's password:

mysql.sql 100% 1039MB 20.8MB/s 00:50

[root@localhost mysql]#

Slave2：

[root@localhost mysql]# mysql < /tmp/mysql.sql

Master:

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+---------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+---------------+----------+--------------+------------------+

| binlog.000003 | 365 | | |

+---------------+----------+--------------+------------------+

1 row in set (0.00 sec)

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

Slave2：

mysql> change master to master_host='192.168.18.107', master_port=3306, master_user='ab', master_password='123', master_log_file='binlog.000003',master_log_pos=365;

Query OK, 0 rows affected (0.05 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

测试：

mysql> show slave status \G

在出结果的数据中，以下语句为yes成功

Slave_IO_Running: Yes

Slave_SQL_Running: Yes