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

This article focuses on "synchronous configuration of MySQL database". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the synchronous configuration method of MySQL database.

MySQL is an open source relational database system. Master-slave synchronous replication (Replication) is a process of replicating data from one MySQL database server (master server master) to another server (slave server slave).

Configure the primary server (master)

1. Edit the database configuration file my.cnf or my.ini (), usually in the / etc/ directory.

# vi / etc/my.cnf

Add the following code under [mysqld]:

Log-bin=mysql-bin www.2cto.com

Server-id=1

Innodb_flush_log_at_trx_commit=1

Sync_binlog=1

Binlog-do-db=wordpress

Binlog_ignore_db=mysql

The 1 in server-id=1 can be defined anywhere, as long as it is unique.

Binlog-do-db=wordpress means that only wordpress is backed up.

Binlog_ignore_db=mysql indicates that the backup mysql is ignored.

Without binlog-do-db and binlog_ignore_db, that means backing up all of them.

2. Then restart MySQL:

# service d restart

3. Log in to MySQL server.

# mysql-uroot-p

Create a new user-assigned "REPLICATION SLAVE" permission on the primary server. You don't need to give any other authority. In the following command, replace X.X.X.X with the IP of the slave server.

Mysql > CREATE USER 'user'@' X.X' IDENTIFIED BY 'password'

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'user'@'X.X.X.X' IDENTIFIED BY' password'

Execute the following command to lock the database to prevent data from being written.

Mysql > FLUSH TABLES WITH READ LOCK

5. Exit the mysql command line and export the database

# mysqldump-u root-p123456-- all-databases > / root/all.sql

6. Transfer the database file all.sql to the slave server using the scp command.

# scp / root/all.sql root@www.example.com:/root

7. Connect to the database again and enter the mysql command line to view the master status.

Mysql > SHOW MASTER STATUS

Please make a note of the information displayed and the configuration will be used from the server.

+-+-- +-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+-- +-+

| | mysql-bin.000001 | 253 | dbispconfig | mysql | |

+-+-- +-+

1 row in set (0.00 sec)

8. Unlock the data table.

Mysql > UNLOCK TABLES

Www.2cto.com

Configure slave server (slave)

Log in from the server.

1. Import the database of the master server.

# mysql-u root-p123456

CHANGE MASTER TO

MASTER_HOST='X.X.X.X'

MASTER_USER='user'

MASTER_PASSWORD='password'

MASTER_PORT=3306

MASTER_LOG_FILE='mysql-bin.000001

MASTER_LOG_POS=253

MASTER_CONNECT_RETRY=10

Note:

MASTER_HOST: the IP of the primary server.

MASTER_USER: the user name established when the primary server is configured

MASTER_PASSWORD: user password

MASTER_PORT: the mysql port of the master server. If it has not been modified, it is OK by default.

Here master_log_file and master_log_pos are the results of the previous show master status.

5. Start slave process.

Mysql > START SLAVE

Mysql > SHOW SLAVE STATUS; if a record can be found, the configuration is successful

6. Check the log of mysql, which is usually in the / var/log/ directory. If you start successfully, you should see a log similar to the following. Www.2cto.com

091104 8:42:02 [Note] Slave I amp O thread: connected to master 'root@X.X.X.X:3306?, replication started in log' mysql-bin.000001? At position 98

Now the master server and slave server are configured. In addition, you may need to set the expiration time of the database binary log on the primary server, which can be set by using the parameter expire_logs_days in the configuration file.

Java spring transaction setting, method header setting

@ Transactional (readOnly=true)

Java connection driver Settings

Jdbc.connection.driver=com.mysql.jdbc.ReplicationDriver

Jdbc.connection.url=jdbc:mysql:replication://192.168.202.190192.168.202.190/job?useUnicode=true&characterEncoding=utf-8

At this point, I believe you have a deeper understanding of the "synchronous configuration method of MySQL database". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.