mysql 主從複製 詳解

標籤：mysql   伺服器   作業系統   軟體版本   style   

作業系統 Centos 6.4 x86_64

軟體版本 mysql 5.5.37

一 主-從複製 （非同步）

當主-從伺服器都剛完成mysql安裝，沒有資料

主伺服器 192.168.200.127

從伺服器 192.168.200.128

1 時間同步

[[email protected] ~]# ntpdate ntp.api.bz[[email protected] ~]# ntpdate ntp.api.bz

 

2 配置master

 

1 修改設定檔

sql_log_bin=onlog_bin=mysql-binserver-id=1innodb_file_per_table=1

2授權複製使用者

mysql> grant replication slave on *.* to  identified by ;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

3重啟mysql服務

[[email protected] ~]# service mysqld restartShutting down MySQL. SUCCESS! Starting MySQL.. SUCCESS!

3 配置slave

1 修改設定檔

[[email protected] ~]# vim /etc/my.cnf [mysqld]relay_log = relay-logserver-id = 10

 

2 重啟mysql服務

 

[[email protected] ~]# service mysqld restartShutting down MySQL. SUCCESS! Starting MySQL.. SUCCESS!

 

3 串連主伺服器

 

主伺服器

mysql> show master status;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000002 |      107 |              |                  |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

 

從伺服器

 

mysql> change master to master_host=‘192.168.200.127‘,master_user=‘user‘,master_password=‘redhat‘;Query OK, 0 rows affected (0.01 sec)mysql> start slave;mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event   Master_Host: 192.168.200.127                  Master_User: user                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000002          Read_Master_Log_Pos: 186               Relay_Log_File: relay-log.000003                Relay_Log_Pos: 332        Relay_Master_Log_File: mysql-bin.000002             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: 186              Relay_Log_Space: 857              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: 0Master_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: 11 row  set (0.00 sec)

 

4 主從複製測試

 

master

mysql> create database db;mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || db                 || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec

 

slave

 

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || db                 || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)

二 當主伺服器已有資料

1 master 備份

[[email protected] ~]# mysqldump -uroot --all-databases --lock-all-table --master-data=2 --events > all.sql

 

2 複製sql檔案至從伺服器

 

[[email protected] ~]# scp all.sql 192.168.200.128:/tmp/

 

3 salve 恢複

 

mysql> source /tmp/all.sql

 

4 查看備份二進位檔案位置

 

[[email protected] ~]# less all.sql -- CHANGE MASTER TO MASTER_LOG_FILE=, MASTER_LOG_POS=809;

 

5 複製

 

mysql> change master to master_host=‘192.168.200.127‘,master_user=‘user‘,master_password=‘redhat‘,master_log_file=‘mysql-bin.000002,master_log_pos=809;mysql> start slave;mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for  master to send event Master_Host: 192.168.200.127                  Master_User: user                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000002          Read_Master_Log_Pos: 809               Relay_Log_File: relay-log.000002                Relay_Log_Pos: 253        Relay_Master_Log_File: mysql-bin.000002             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: 809              Relay_Log_Space: 403              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: 0Master_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: 11 row  set (0.00 sec)

三 半同步複製

1 安裝外掛程式

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_slave.so‘ ;mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1; mysql> SET GLOBAL rpl_semi_sync_master_timeout = 2000;

 

2 修改設定檔

 

[[email protected] ~]# vim /etc/my.cnf rpl_semi_sync_master_enabled = 1rpl_semi_sync_master_timeout = 2000

 

3 重啟服務

 

[[email protected] ~]# service mysqld restartShutting down MySQL. SUCCESS! Starting MySQL.. SUCCESS!

 

 

slave

1安裝外掛程式

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ;mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1; mysql> STOP SLAVE IO_THREAD;mysql> START SLAVE IO_THREAD;

 

2修改設定檔

 

[[email protected] mysql]# vim /etc/my.cnfrpl_semi_sync_slave_enabled = 1

 

3 重啟服務

 

[[email protected] mysql]# service mysqld restartShutting down MySQL. SUCCESS! Starting MySQL.. SUCCESS!

 

4 查看狀態

 

master

mysql> show global status like ‘rep_semi%‘;+--------------------------------------------+-------+| Variable_name                              | Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients               | 1     || Rpl_semi_sync_master_net_avg_wait_time     | 0     || Rpl_semi_sync_master_net_wait_time         | 0     || Rpl_semi_sync_master_net_waits             | 0     || Rpl_semi_sync_master_no_times              | 0     || Rpl_semi_sync_master_no_tx                 | 0     || Rpl_semi_sync_master_status                | ON    || Rpl_semi_sync_master_timefunc_failures     | 0     || Rpl_semi_sync_master_tx_avg_wait_time      | 0     || Rpl_semi_sync_master_tx_wait_time          | 0     || Rpl_semi_sync_master_tx_waits              | 0     || Rpl_semi_sync_master_wait_pos_backtraverse | 0     || Rpl_semi_sync_master_wait_sessions         | 0     || Rpl_semi_sync_master_yes_tx                | 0     |+--------------------------------------------+-------+14 rows in set (0.00 sec)

 

 

slave

mysql> show global status like ‘rpl_semi%‘ ;+----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| Rpl_semi_sync_slave_status | ON    |+----------------------------+-------+1 row in set (0.00 sec)

 

 

半同步複製完成