MySQL implements master-slave replication

Today talk about how MySQL master-slave replication can do!

Preparatory work:

1. Two virtual machines: I'm using CENTOS5.5,IP addresses, respectively, 192.168.1.101 and 192.168.1.105;

101 master server, 105 do from the server (both have installed the same version of MySQL);

2. Native Environment: Apache+php+mysql

All right, now, let's see how this sounds big on the master copy.

Principle: MySQL to achieve master-slave replication, in fact, relying on the binary log, that is: Assume the primary server called a, from the server called B; master-slave replication is

b follow a, what a does, b do. So how does B synchronize the action of a? Now A has a log function, the action of adding or deleting the changes you have made

All recorded in the log, b just need to get this log, according to the log above the action to apply to yourself. This enables the master-slave replication.

Extension: MySQL also has a log called: Slow log

You can set a time, and all SQL executed over this time will be logged. This allows the slow log to quickly find the SQL bottleneck in the site to optimize.

We have time to study, here is not much to do introduction.

Implementation steps:

1. First modify the MySQL configuration file so that it supports the binary logging feature.

Open the MySQL configuration file for the primary server: my.conf

Code: # VI/ETC/MY.CNF

Add the following three lines of code:

Parameter explanation: Log-bin=mysql-bin//name MySQL binary log as Mysql-bin

binlog_format=mixed//binary log format, there are three kinds: statement/row/mixed, specifically not to do more explanation, here use mixed

SERVER-ID=101//Set a unique ID for the server to differentiate, here use the last one of the IP address to act as Server-id

Configuration complete: Wq Save, restart MySQL

Restart MySQL command: # service mysqld Restart

Similarly, go to my.cnf from server, configure from server, repeat step 1,

The only difference is that the Server-id to be changed from the server IP tail, that is, server-id=105; the other two are the same, save and restart MySQL;

2. On the primary server to assign an account from the server, like a key, from the server to hold the key to the master server to share the primary server log files.

Go to the MySQL interface of the master server,

Command: # mysql-u root-p 111111//I am here MySQL account is root, password is 111111

Under the MySQL operator interface, enter the following line of commands:

GRANT replication Slave on * * to ' slave ' @ '% ' identified by ' 111111 ';

3. View the information for the primary server bin log (these values are logged after execution, and do not do anything to the primary server until the server is configured, because these values change each time the database is manipulated).

4. Set the slave server

Go to MySQL from server

Command: # mysql-u root-p111111

Close slave (if you have previously configured the master and slave, be sure to close first)

Command: Stop slave;

To start the configuration:

Enter the following code:

Parameter explanation: Master_host: Sets the IP address of the primary server to connect to

Master_user: Set the user name of the primary server to connect to

Master_password: Set the password for the primary server to connect to

Master_log_file: Sets the log name of the bin log of the primary server to be connected, which is the information obtained in step 3rd

Master_log_pos: Sets the record location of the bin log of the primary server to which you want to connect, i.e. the information obtained in step 3rd, (note that the last item does not need to be quoted.) Otherwise, the configuration fails)

Starting from the server configuration, start from the server:

Command: Start slave;

5. See if the configuration was successful:

Command: show slave status;

Both of the above are yes, stating that the configuration was successful, otherwise repeat the previous steps.

OK, here MySQL master-slave replication is configured, in fact, understand the principle is still very simple,

Here will not give you a test, you can test yourself after the configuration, what the problem,

Welcome to the comment area to shoot bricks ha!

MySQL implements master-slave replication