MySQL master-slave synchronization in Linux

Primary server:10.236.51.151

From server:10.236.51.152

Install MySQL

Yum-y Install Mysql-devel Mysql-server

Run the Security Settings Wizard

Mysql_secure_installation

Primary server

CP/USR/SHARE/MYSQL/MY-LARGE.CNF/ETC/MY.CNF (My-large is a file that notes a lot of configuration )

Modify the /etc/my.cnf file on the master side

server_id = 1( for 1 means master,2 represents slave)
binlog-do-db =Test(Testrepresents the database to be synchronized ) , if there are multiple databases, one row per database
binlog-ignore-db= MySQL settings do not need to synchronize the database, one row per database (generally this can not be written) (if none of the 2 articles is written, it is all synchronized)
Log-bin = Mysql-bin

go to MySQL (don't forget;)

Create a database Test
CREATE DATABASE Test ;
Create a synced user

permissions.
grant replication  slave on *.* to  ' replication ' @ ' 10.236.51. 152  identified by  ' password ';

mysql> flush Privileges;
Restart MySQL

Mysql> flush tables with read lock;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show Master Status\g

1. Row ***************************

file:mysql-bin.000002 (this is used in the configuration of slave)

position:329(This is used in the configuration of slave )

Binlog_do_db:test

Binlog_ignore_db:mysql

1 row in Set (0.00 sec)

mysql> unlock tables;

Note: The purpose of the lock table here is to keep the new data in the production environment so that the synchronization location is located from the server. After the initial sync is complete, remember to unlock it.

From the server

Cp/usr/share/mysql/my-large.cnf/etc/my.cnf

Modify the /etc/my.cnf file on the master side

[Mysqld]

Server-id = 2

Log-bin = Mysql-bin

REPLICATE-DO-DB = Test

Replicate-ignore-db = Mysql,information_schema

Restart MySQL

Specify the synchronization location with the change master statement

mysql> Change Master to master_host= ' 10.236.51.151 ', master_user= ' replication ', master_password= ' password ', master _log_file= ' mysql-bin.000002 ', master_log_pos=0;

ERROR 1198 (HY000): This operation cannot is performed with a running slave; Run STOP SLAVE First

mysql> stop slave;

Query OK, 0 rows Affected (0.00 sec)

mysql> Change Master to master_host= ' 10.236.51.151 ', master_user= ' replication ', master_password= ' password ', Master_log_file= 'mysql-bin.000002 ', master_log_pos=329;

Query OK, 0 rows affected (0.01 sec)

mysql> start slave;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show Slave Status\g

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:10.236.51.151

Master_user:replication

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000002

read_master_log_pos:329

relay_log_file:mysqld-relay-bin.000002

relay_log_pos:474

relay_master_log_file:mysql-bin.000002

Slave_io_running:yes

Slave_sql_running:yes

Replicate_do_db:test

Replicate_ignore_db:mysql

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:329

relay_log_space:630

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:

MySQL master-slave synchronization in Linux