mysql利用mysql_multi配置一台主機的主從複製

1.環境

Server version: 5.5.24-0ubuntu0.12.04.1-log (Ubuntu)

mysql是通過 apt-get方式安裝的

 

2.建立主從庫的初始化資料庫

mysql_install_db --datadir=/var/lib/mysql1 --user=mysql

mysql_install_db --datadir=/var/lib/mysql1 --user=mysql

如果執行出錯，那請到/etc/apparmor.d/中編輯usr.sbin.mysqld,添加如下內容

##############mysql1###############
/var/log/mysql1.log rw,
/var/log/mysql1.err rw,
/var/lib/mysql1/ r,
/var/lib/mysql1/** rwk,
/var/log/mysql1/ r,
/var/log/mysql1/* rw,
#/var/run/mysqld/mysqld1.pid w,
#/var/run/mysqld/mysqld1.sock w,
#/run/mysqld/mysqld1.pid w,
#/run/mysqld/mysqld1.sock w,

##############mysql2###############
/var/log/mysql2.log rw,
/var/log/mysql2.err rw,
/var/lib/mysql2/ r,
/var/lib/mysql2/** rwk,
/var/log/mysql2/ r,
/var/log/mysql2/* rw,
#/var/run/mysqld/mysqld2.pid w,
#/var/run/mysqld/mysqld2.sock w,
#/run/mysqld/mysqld2.pid w,
#/run/mysqld/mysqld2.sock w,

然後重啟下mysql，在執行以上語句即可成功建立初始資料庫

2.在/etc/mysql目錄下建立mysqld_multi.cnf

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root　　//此使用者名稱和密碼要和資料庫執行個體的使用者名稱和密碼對應，否則將無法關閉資料庫執行個體
#password = multipass
log = /var/log/mysqld_multi.log

[mysqld1]
socket = /tmp/mysql1.sock
port = 3307
pid-file = /tmp/mysql1.pid
datadir = /var/lib/mysql1
user = mysql
#log = /var/log/mysql1.log

log-bin = /var/lib/mysql1/mysql-bin-3307
binlog_do_db = m_test1_3307
server-id = 1

[mysqld2]
socket = /tmp/mysql2.sock
port = 3308
pid-file = /tmp/mysql2.pid
datadir = /var/lib/mysql2
user = mysql
#log = /var/log/mysql2.log

#log-bin = /var/lib/mysql2/mysql-bin-3308
replicate_do_db = m_test1_3307
server-id = 2

3.啟動資料庫執行個體

mysqld_multi --defaults-extra-file=/etc/mysql/mysqld_multi.cnf start #啟動

mysqld_multi --defaults-extra-file=/etc/mysql/mysqld_multi.cnf stop #關閉

mysqld_multi --defaults-extra-file=/etc/mysql/mysqld_multi.cnf report #查看狀態

4.分別在兩個執行個體中建要同步複製的資料庫和表,如配置中的m_test1_3307

最好都用相同的資料庫名和表名（略）

登陸如： mysql -uroot -p -h127.0.0.1 -P3307

5.主庫上相關設定

建立用於同步複製的使用者

grant replication slave on *.* to 'slave3307'@'127.0.0.1' identified by '3307';

flush privileges;此時查看主從狀態，如下show master status;

+-----------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+----------+--------------+------------------+
| mysql-bin-3307.000004 | 107 | m_test1_3307 | |
+-----------------------+----------+--------------+------------------+

記住表中的參數，設定從庫是需要用到

6.設定從庫

change master to master_host='127.0.0.1',master_port=3307,master_user='slave3307',master_password='3307',master_log_file='mysql-bin-3307.000004',master_log_pos=107;

start slave;

show slave status\G;

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: slave3307
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin-3307.000004
Read_Master_Log_Pos: 107
Relay_Log_File: mysql2-relay-bin.000010
Relay_Log_Pos: 258
Relay_Master_Log_File: mysql-bin-3307.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: m_test1_3307
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 566
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1

出現以上資訊，表示同步成功了，可以在主庫表中插入資料，再查看從庫對應的表中是否有相同的資料測試下。

注意事項：

設定關閉多個執行個體

mysql -u root -S /tmp/mysql1.sock -p
mysql> grant shutdown on *.* to root@'localhost' identified by 'multipass';

log-slave-updates #啟用從庫日誌，這樣可以進行鏈式複製 

read-only=1 #從庫是否唯讀，0表示可讀寫，1表示唯讀 

relay-log-purge=1 #複製完的sql語句是否立即從中繼日誌中清除，1表示立即清除