Mysql服务器的主辅数据同步_MySQL
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql服务器的主辅数据同步_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4388字，纯文字阅读大概需要7分钟。
内容图文
bitsCN.com
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： www.bitsCN.com
[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： [确定] www.bitsCN.com
启动 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
内容总结
以上是互联网集市为您收集整理的Mysql服务器的主辅数据同步_MySQL全部内容，希望文章能够帮你解决Mysql服务器的主辅数据同步_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。