MySQL Master-Slave synchronization

Two Linux systems install the same version of the MySQL database.

Configuration of two databases it must be different to enable both binary logs and Server-id,server-id.

Then restart the MySQL database.

Authorization to slave from the database server

Mysql> GRANT REPLICATION SLAVE on * * to ' REPLICATION ' @ ' Slave-ip ' identified by ' REPLICATION ';

View the binary log information for master.

Querying the primary database state

Mysql> Show master status;

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

| mysql-bin.000005 | 261 | | |

+------------------+----------+--------------+------------------+

Records the values of FILE and Position, which need to be used in the subsequent operations from the server.

2.2 Configuration from the server

Modify the configuration file from the server/opt/mysql/etc/my.cnf

Modify Server-id = 1 to Server-id = 10, and make sure that the ID is not used by another MySQL service.

Start the MySQL service

/opt/mysql/init.d/mysql start

Managing MySQL server via command line login

/opt/mysql/bin/mysql-uroot-p ' New-password '

Executing a synchronous SQL statement

Mysql> Change Master to

Master_host= ' 192.168.10.130 ',

Master_user= ' Rep1 ',

master_password= ' Password ',

Master_log_file= ' mysql-bin.000005 ',

master_log_pos=261;

Start slave synchronization process after correct execution

mysql> start slave;

Master-Slave synchronization check

Mysql> Show Slave Status\g

==============================================

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

Slave_io_state:

master_host:192.168.10.130

Master_user:rep1

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000005

read_master_log_pos:415

relay_log_file:localhost-relay-bin.000008

relay_log_pos:561

relay_master_log_file:mysql-bin.000005

Slave_io_running:yes

Slave_sql_running:yes

replicate_do_db:

............... To omit a number of ......

Master_server_id:1

1 row in Set (0.01 sec)

==============================================

The value of slave_io_running and slave_sql_running must be yes to indicate that the status is normal.

If the primary server already has application data, the following processing is required for master-slave replication:

(1) The primary database for the lock table operation, do not let the data to write again action

Mysql> FLUSH TABLES with READ LOCK;

(2) View Primary database status

Mysql> Show master status;

(3) Copying data files

Copy the data file of the master server (the entire/opt/mysql/data directory) to the slave server, it is recommended to compress the tar archive and then upload it to the server.

(4) Canceling the primary database lock

Mysql> UNLOCK TABLES;

If it appears:

When slave_io_running:connecting, there are these things:

1,master can not connect, it may be iptables, or the network is not available, first telnet to the master MySQL port is normal.

2. Is the user name and password for connection master correct?

3, connect the binary log File used by Master | Are these two values of the Position correct?

That's three questions. If the installation is not a problem, the three problems, the solution can be.

MySQL Master-Slave synchronization