Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to achieve data synchronization in Mysql database. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. Installation configuration

The two servers, installed with Mysql respectively, are installed in the / usr/local/mysql directory (the installation steps are omitted, please refer to the relevant documentation). The IP of the two servers is 192.168.0.1 and 192.168.0.2 respectively. We use 192.168.0.1 as the master database and 192.168.0.2 as the slave server. We use one-way synchronization, that is, the data of master is the master data. Then slave takes the initiative to go to master to synchronize the data back.

The configuration of the two servers is the same, let's copy the key configuration files, the default configuration file is in the / usr/local/mysql/share/mysql directory, there are my-large.cnf, my-medium.cnf, my-small.cnf and other writers, we just test, use my-medium.cnf on it. After mysql is installed, the default configuration file is specified in the database storage directory, and we use 4.1.X, so the configuration file should be in the / usr/local/mysql/var directory, so copy the configuration file:

Cp / usr/local/mysql/share/mysql/my-medium.cnf / usr/local/mysql/var/my.cnf

Both servers do the same copy of the configuration file.

two。 Configure the Master server

If we are going to configure 192.168.0.1 as the primary mysql server (master), then we need to consider which database we need to synchronize and which user synchronization we need. For simplicity, we use root users for synchronization, and we only need to synchronize the database abc.

Open the configuration file:

Vi / usr/local/mysql/var/my.cnf

Find the information:

# required unique id between 1 and 2 ^ 32-1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

Server-id = 1 / / 1 is master,2 and salve

Add two lines:

Sql-bin-update-same / / synchronous form

Binlog-do-db = abc / / Database to be synchronized

Restart the mysql server of 192.168.0.1:

/ usr/local/mysql/bin/mysqladmin shutdown

/ usr/local/mysql/bin/mysqld_safe-user=mysql &

3. Configure the Slave server

Our slave server mainly takes the initiative to synchronize data back to the master server, and we edit the configuration file:

Vi / usr/local/mysql/var/my.cnf

Find similar information as follows:

# required unique id between 1 and 2 ^ 32-1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

Server-id = 1

Change the above server-id to 2 and add some information:

Server-id = 2 / / this Mysql is a slave server

Master-host = 192.168.0.1 / / IP of the master server

Master-user = root / / user who connects to the master server

Master-password =''/ / password to connect to the master server

Master-port = 3306 / / connection port

Master-connect-retry = 10 / / number of retries

Replicate-do-db = abc / / Database to be synchronized

Form of log-slave-updates / / synchronization

Restart the mysql server of 192.168.0.2:

/ usr/local/mysql/bin/mysqladmin shutdown

/ usr/local/mysql/bin/mysqld_safe-user=mysql &

4. Test installation

First, take a look at slave's host log:

Cat / usr/local/mysql/var/xxxxx_err (xxx is the hostname)

Check whether the connection is normal, and it is successful to see a message like this.

051031 11:42:40 mysqld started

051031 11:42:41 InnoDB: Started; log sequence number 0 43634

/ usr/local/mysql/libexec/mysqld: ready for connections.

Version: '4.1.15 socket:' / tmp/mysql.sock' port: 3306 Source distribution

051031 11:42:41 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log'. / new4-relay-bin.000001' position: 4

051031 11:43:21 [Note] Slave I amp O thread: connected to master 'root@192.168.0.1:3306', replication started in log' FIRST' at position 4

View information on Master

/ usr/local/mysql/bin/mysql-u root

View master status:

Mysql > show master status

View the mysql process information under Master:

Mysql > show processlist

View information on slave:

/ usr/local/mysql/bin/mysql-u root

View slave status:

Mysql > show slave status

View the mysql process information under slave:

Mysql > show processlist

You can check whether the setting is successful by setting up the table structure and inserting the data in master's abc library, and then checking whether slave has synchronized the data.

After reading the above, have you mastered the method of data synchronization in Mysql database? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.