環境:master: 192.168.240.131
slave: 192.168.240.130
需要複制的資料庫:test
1. master伺服器啟動mysql,
a) #mysql –uroot –proot
b) 建立一個有複制權限的使用者,隻限slave遠端連接配接通路.
i. mysql>grant replication slave on *.* to [email protected] identified by ‘password’;
ii. mysql>flush privileges;
c) mysql>flush tables with read lock; #鎖定master伺服器所有表的寫入。
d) 重新打開一終端,備份要複制的資料庫。
i. #tar -zcvf test.tar.gz /data/mysql_db/test/ //test所在路徑/data/mysql_db/test/即一個庫。
ii. 将test.tar.gz複制到從伺服器上并減壓到相應的目錄下
e) 傳回上一終端。
i. mysql>show master status;
+------------------+----------+--------------+------------------+
| file | position | binlog_do_db | binlog_ignore_db |
|mysql-bin.000013| 106 | test | |
1 row in set (0.01 sec)
其中mysql-bin.000013和106二個值将是slave與master的同步點。
f) 給資料庫解鎖(當備份完成後) mysql>unlock tables;
g) 編輯mysql的配置檔案。vim /etc/my.cnf 設定這三個參數,沒有的添加,有的直接更改即可。
log-bin=mysql-bin
server-id = 1
binlog-do-db=test
儲存退出。
2. slave伺服器配置
a) 将從master中備份的庫解壓到相應路徑下(退庫的導入)
i. #tar zxvf test.tar.gz
b) 修改my.cnf
server-id=2
master-host=192.168.240.131
master-user= navy
master-password= password
log-bin=
3. 重新開機master, slave的mysql服務
a) 注意順序,先重新開機master-à 然後是slave.
b) slave伺服器重新開機後,登入mysql
mysql> stop slave;
query ok, 0 rows affected (0.00 sec)
mysql> change master to
-> master_host='192.168.0.3',
-> master_user='navy',
-> master_password='password',
-> master_log_file='mysql-bin.000013',
-> master_log_pos=106;
query ok, 0 rows affected (0.02 sec)
mysql> start slave;
mysql> show slave status\g;
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: 192.168.240.131
master_user: navy
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000013
read_master_log_pos: 98
relay_log_file: alan-relay-bin.000002
relay_log_pos: 235
relay_master_log_file: mysql-bin.000013
slave_io_running: yes
slave_sql_running: yes
replicate_do_db:
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: 98
relay_log_space: 235
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
error: no query specified
當這個參數都為yes時,證明主從複制成功
slave_io_running: yes slave_sql_running: yes
設定隻讀:
alter database dbname set read_only
恢複讀寫:
alter database db_name set read_write