Mysql implements master-slave synchronization _ MySQL

Mysql implements master-slave synchronization to bitsCN.com

Mysql implements master-slave synchronization

Implement master-slave synchronization in mysql:

Step 1: Install a mysql instance on each of the two machines (unbutu and mysql5.5 in my testing system). if there is only one machine, make the mysql Port different (3306, 3307 ).

Step 2: Find the mysql my. cnf file.

$ Cd/etc/mysql to enter the directory.

$ Vim my. cnf is read-only and cannot be modified. $ Sudo vim my. cnf required

Step 3: modify the configuration items in my. cnf:

1. perform the following operations:

Change bind-address = 127.0.0.1 to bind-address = 0.0.0.0.

Set # server-id = 1

# Log_bin =/var/log/mysql/mysql-bin.log

.

Add: character-set-server = UTF-8 # ensure consistent encoding.

After filling in, save and exit.

Step 4:

Log on to and create a server load balancer instance with the root permission:

$ Mysql-uroot-p

Mysql> grant replication slave, replication client on *. * to liang @ '192. 168.10.21 'indntified by 'Shanghai ';

Mysql> grant replicatin slave on *. * to liang @ '192. 168.10.21 'indntified by 'Shanghai ';

Step 5: Restart the mysql service.

$ Sudo/etc/init. d/mysql restart # if it cannot be started normally, check the configuration items of my. cnf and mysql error logs.

Error log location:/var/log/mysql/error. log

The master has been configured.

Slave configuration.

Step 6: modify the my. cnf configuration file of slave.

As follows:

Bind-address = 0.0.0.0

Server-id = 2

Master-host = 192.168.10.24

Master-user = liang

Master-password = liang

Master-port = 3306

Log_bin =/var/log/mysql/mysql-bin.log

Log-slave-updates

Skip-slave-start

Character-set-server = utf8

Save and exit.

Step 7: Restart the slave mysql server (same as step 5)

I encountered an error when I restarted and could not start mysql. after checking the log, I found that the following error (omitted temporarily) was found because the error log is not on the local machine. Make up later, Hey.

The configuration is changed to the following:

Server-id = 2

# Master-host = 192.168.10.24

# Master-user = liang

# Master-password = liang

# Master-ports = 3306

Log_bin =/var/log/mysql/mysql-bin.log

Log-slave-updates

Skip-slave-start

Character-set-server = utf8

Save and restart mysql. OK.

Enter myslq and run it in mysql:

Slave start;

Step 8: execute in the master:

Mysql> show master status/G;

The displayed content is as follows:

Step 9: run the following command in slave:

Mysql> change master to master_host = '192. 168.10.24 ', master_user = 'Shanghai', master_password = 'Shanghai', master_log_file = 'MySQL-bin.000007', master_log_pos = 192;

Master_log_file and master_log_pos are shown in step 8.

Mysql> slave start;

Step 10: execute the following command in the master:

Mysql> show slave status/G;

Please refer to Slave_IO_Running: YES

Slave_ SQL _Running: YES

OK. the configuration is successful.

Please see Slave_IO_Running: Connectiong. please view the following error information or log files.

Step 2: Test

Create a database in the primary database:

Mysql> create database ccc;

Check in slave. if the ccc database exists, the synchronization is successful.

You can also create tables, modify tables, and add records. Everything is OK.

Finally, thank you for your friends on the Internet. With your help, you can easily complete the process.

BitsCN.com