Mysql服务器的主辅数据同步

来源:中国IT实验室 作者:中国IT实验室
  

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

 


时间:2013-03-03 17:38 来源:中国IT实验室 作者:中国IT实验室 原文链接

好文,顶一下
(0)
0%
文章真差,踩一下
(0)
0%
------分隔线----------------------------


把开源带在你的身边-精美linux小纪念品
无觅相关文章插件,快速提升流量