程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> Mysql服務器的主輔數據同步

Mysql服務器的主輔數據同步

編輯:MySQL綜合教程

Mysql服務器的主輔數據同步   例題:將A服務器作為主服務器(Master),B1和B2為輔服務器(Slave), 怎麼來將主服務器的數據同步到輔服務器呢,下面我們來看。   www.2cto.com   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.2cto.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.2cto.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 [email protected]:/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. [email protected]'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 [email protected]:/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. [email protected]'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
 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved