MySQL master-slave replication detailed

Operating system Centos 6.4 x86_64

Software version MySQL 5.5.37

One master-from replication (asynchronous)

When the master-slave server has just completed MySQL installation, no data

Primary server 192.168.200.127

From server 192.168.200.128

1 time synchronization

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

2 Configuring the Master

1 Modifying a configuration file

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

2 Authorizing replication users

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 Restart MySQL Service

[[Email protected] ~]# service mysqld restartshutting down MySQL. success! Starting MySQL. success!

3 Configuring Slave

1 Modifying a configuration file

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

2 Restart MySQL Service

[[Email protected] ~]# service mysqld restartshutting down MySQL. success! Starting MySQL. success!

3 Connecting to the primary server

Primary server

Mysql> Show Master status;+------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+|      mysql-bin.000002 |              107 |                  | |+------------------+----------+--------------+------------------+1 row in Set (0.00 sec)

From the server

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-slave replication test

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)

Two when the master server already has the data

1 Master Backup

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

2 Copy SQL file to slave server

[Email protected] ~]# SCP all.sql 192.168.200.128:/tmp/

3 Salve Recovery

Mysql> Source/tmp/all.sql

4 Viewing the backup binary file location

[email protected] ~]# less all.sql – change MASTER to master_log_file=, master_log_pos=809;

5 Replication

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              &nbsP 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:  &Nbsp;             last_sql_errno: 0                last_sql_error:    Replicate_Ignore_Server_Ids:               Master_Server_Id: 11 row  set  (0.00 SEC)

Three-half synchronous replication

1 Installing Plugins

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 Modifying a configuration file

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

3 Restart Service

[[Email protected] ~]# service mysqld restartshutting down MySQL. success! Starting MySQL. success!

Slave

1 Installing Plugins

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 Modifying a configuration file

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

3 Restart Service

[[Email protected] mysql]# service mysqld restartshutting down MySQL. success! Starting MySQL. success!

4 Viewing status

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)

Semi-synchronous replication complete