MySQL database master-slave configuration

Primary server 192.168.7.182 Centos6.5 MYSQL5.6.10

From server 192.168.112.7 Centos6.5 MYSQL5.6.10

1. Primary server configuration (192.168.7.182)

Vi/etc/my.cnf

[Mysqld]
Server-id=1
Log-bin=mysql-bin
Binlog-do-db=hello the name of the database that needs to be synchronized (not created in advance)

Save restart MySQL Service

Service MySQL Restart

Master server Configuration Complete

2. View Primary Server information

Mysql> Show Master Status\g
1. Row ***************************
file:mysql-bin.000002
position:120
Binlog_do_db:hello
binlog_ignore_db:
Executed_gtid_set:
1 row in Set (0.00 sec)

3. From the server configuration (192.168.112.7)

Vi/etc/my.cnf
Plus
[Mysqld]
server-id=2
Log-bin=log

Save restart MySQL Service

Service MySQL Restart

Configuration is complete from server

4. You need to establish the relationship between the master and slave servers when the configuration is finished

Mysql>change Master to
Master_host = ' 192.168.7.182 ',
Master_user = ' root ',
master_port=3306,
Master_password= ' 123456 ',
Master_log_file = ' mysql-bin.000002 ', (corresponding to the main server file parameter)
master_log_pos=120; (corresponds to the primary server Position parameter)

5. View Slave Server Status

Mysql> Show Slave Status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.7.182
Master_user:root
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000002
read_master_log_pos:262
relay_log_file:liuchao-relay-bin.000003
relay_log_pos:425
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:262
relay_log_space:600
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
Master_uuid:f242269d-cbaf-11e4-b866-000c29f4cbd9
Master_info_file:/var/lib/mysql/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:
Executed_gtid_set:
auto_position:0
1 row in Set (0.00 sec)

6. Restart the MySQL service after the configuration is finished

Service MySQL Restart

7. Test

Create a Hello database on the 192.168.7.182 (master) server, and you will see that the 192.168.112.7 (slave) server also appears in the Hello database

Operations on the Hello database on the 192.168.7.182 (primary) server will be synchronized to the 192.168.112.7 (slave) server

8. When configuring, note the MySQL version, different version configuration is different

MySQL database master-slave configuration