MySQL Master-slave replication

Before the MySQL installation has been written, there is no repetition of the work.

Here I have prepared two virtual machines (192.168.40.128:mini1 192.168.40.130:mini2), respectively configured to create accounts in this environment Mini1, MINI2

CREATE USER ‘username'@'localhost' IDENTIFIED BY ‘userpassword';

Once you have created your account, you need to give your account remote access permissions

Remember to refresh the permissions after modifying.

We are here to 192.168.40.130 as the slave, to 192.168.40.128-based, respectively, modify their configuration file

Vi/etc/my.cnf

Here the server_id two machines must be different, the other does not matter, binlog-do-db this is required to synchronize the database, I am here to configure the need to synchronize test, two machine configuration needs the same, there are some other configurations, interested can go to check, I am here only for the demo list of the most basic 4, do not affect the environment to build

(Note: Two machines need to synchronize the table in the database must be exactly the same, otherwise it will result in data rollback, slave shutdown, if you can see the data rollback error information to analyze, if the data add failure can be through the "show slave status\g" command to see the cause of the error)

After both machine environments are restarted OK, the next step is to enter the "Show Master Status" command on the main machine to view the main and binary files and position

After getting the binary file name and position of the main machine, go to 192.168.40.130 to stop the slave from the machine:

After successfully stopping slave, you can modify the master configuration item, where Master_host is the host's IP, Master_user is the user who can log on to the host, Master_password the Host account password, Master_log_ File just found in the host binary file name, Master_log_pos just found in the host position

(Remember: The master configuration parameters modified from the machine here are all from the host that needs to synchronize the data, the slave_io_running after the wrong words: it will show no)

When all is properly configured, start slave

Start slave;

When started, enter the "show slave status\g" command to see the relevant state of slave, where both slave_io_running and slave_sql_running must be yes, otherwise data synchronization will not succeed.

Everything is OK, here's a test to see if you can sync the data successfully:

Here I execute the INSERT statement on the 192.168.40.128:

Query on the slave 192.168.40.130:

You can see that the data has been synchronized from the host, indicating that the master-slave replication has succeeded.

(Note: If you want to achieve primary master replication, both machines can achieve the insertion synchronization, as long as the above operation in turn to 192.168.40.130 the main and then execute again can be completed successfully, but the form of the main master or not recommended, will reduce efficiency! ）

MySQL Master-slave replication