MySQL Database Synchronization Configuration

MySQL database synchronization. MySQL is an open-source relational database system. Master-slave Replication is a process that replicates data from a MySQL database server (master server) to another server (slave server. Configure the master server)
1. Edit the database configuration file my. cnf or my. ini (windows), which is usually in the/etc/directory. # Vi/etc/my. cnf adds the following code under [mysqld: log-bin = mysql-bin www.2cto.com server-id = region = 1sync_binlog = 1binlog-do-db = wordpressbinlog_ignore_db = mysql server-id = 1 can be defined as long as it is unique. Binlog-do-db = wordpress indicates that only wordpress is backed up. Binlog_ignore_db = mysql indicates that the backup mysql is ignored. If binlog-do-db and binlog_ignore_db are not added, all databases are backed up. 2. restart MySQL: # service mysqld restart 3. log on to the MySQL server. # Mysql-uroot-p create a user on the master server to grant the "replication slave" permission. You do not need to grant other permissions. In the following command, replace X. X with the IP address of the slave server. Mysql> create user 'user' @ 'x. x. x. X 'identified BY 'Password'; mysql> grant replication slave on *. * TO 'user' @ 'x. x. x. X 'identified BY 'Password'; 4. Run the following command to lock the database to prevent data writing. 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. Use the scp command to transmit all database files. SQL to slave server. # Scp/root/all. SQL root@www.example.com:/root 7, connect to the database again to enter the mysql command line to view the master status. Mysql> show master status; write down the information displayed and use it on the slave 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 the slave server (slave) to log on to the slave server. 1. Import the database of the master server. # Mysql-u root-p123456 </root/all. SQL 2. Edit the configuration file my. cnf: Add server-id = 2 under [mysqld] Note: 2 can be defined by yourself, as long as it is unique. 3. Save the file and restart mysqld. # Service mysqld restart 4. log on to the mysql server and run the following command. Mysql> change master TOMASTER_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 address of the master server. MASTER_USER: username set up When configuring the master server MASTER_PASSWORD: User Password MASTER_PORT: master server mysql port. If it has not been modified, it will be by default. Master_log_file and master_log_pos are the results of the previous show master status. 5. Start the slave process. Mysql> start slave; mysql> show slave status; if a record can be found, the configuration is successful. 6. view the mysql log, which is usually in the/var/log/directory, if the startup is successful, you will see a log similar to the following. Www.2cto.com 091104 8:42:02 [Note] Slave I/O thread: connected to master 'root @ X. X: 3306 ?, Replication started in log 'mysql-bin.000001? At position 98 is now configured on the master server and slave server. In addition, you may need to set the expiration time of the database binary log of the master server. You can use the expire_logs_days parameter in the configuration file to set the expiration time. Java spring transaction settings, method header settings @ Transactional (readOnly = true) java connection driver settings jdbc. connection. driver = com. mysql. jdbc. replicationDriverjdbc. connection. url = jdbc: mysql: replication: // 192.168.202.190, 192.168.202.190/job? UseUnicode = true & characterEncoding = UTF-8 prepared by yjflinchong