MySQL versions later than 3.23.15 provide the database replication function. with this function, two databases can be synchronized, in master-slave mode, and backed up to each other. Implement synchronous data backup. The settings of the database synchronization replication function are reflected in the mysql settings file. Mysql configuration file (usually my. cnf) in the Linux environment in etcmy. cnf or in mysql with Redhat
MySQL versions later than 3.23.15 provide the database replication function. with this function, two databases can be synchronized, in master-slave mode, and backed up to each other. Implement synchronous data backup.
The settings of the database synchronization replication function are reflected in the mysql settings file. Mysql configuration file (usually my. cnf) in the Linux environment in/etc/my. cnf or under the mysql User's home directory my. cnf.
Setting method:
Operating system: RedHat Linux 9 mysql: mysql-standard-4.0.18-pc-linux-i686.tar.gz
A Server: 192.168.0.1 master Server master
B Server: 192.168.0.10 slave Server slave
MySQL has been installed on A and has A database, INSTALL mysql-standard-4.0.18 on B, the installation steps can be squashed and INSTALL mysql by INSTALL-BINARY method, I will then start mysql.
Server A settings
# Mysql-u root-p Master opens an account dbbackup password 123456 to IP address: 192.168.0.10 has the file processing permission. mysql> grant all on *. * TO dbbackup@192.168.0.10 identified by '000000' mysql> exit
# Mysqladmin-u root-p shutdown
Back up all the databases in the Master. the tar command is usually used. (Note: When tar is used, MySQL must be stopped)
# Tar zcvf/tmp/mysql.tar.gz/usr/local/src/mysql/data
Modify/etc/my. cnf on machine
[Mysqld]
Log-bin (generate the. index file. # Set the log to be recorded. you can set log-bin = c: \ mysqlbak \ mysqllog to set the Directory of the log file,
# Mysqllog is the name of the log file. mysql will create several log files with different extensions and file names mysqllog .)
Server-id = 1
SQL-bin-update-same
Binlog-do-db = forimage (# specify the database for which the log is required as forimage)
Start server A mysql: Bin/mysqld-safe-user = mysql server-id = 1 & (you must specify the server-id)
In this case, the index file is generated because the log-bin parameter is added. the. index file is in the/usr/local/src/mysql/data Directory. Change log of the archive record database. # mysql-u root-p use the show master status command to check the log.
Server B settings
Set/etc/my. cnf
[Mysqld]
Master-host = 192.168.0.1
Master-user = dbbackup (# synchronize user accounts)
Masters-password = 123456
Master-port = 3306
Server-id = 2
Master-connect-retry = 60 (the default retry interval is 60 seconds)
Replicate-do-db = forimage (only for database forimage updates)
Log-slave-updates
Copy/tmp/mysql.tar.gz on A to data/under the MySQL installation directory on B, and decompress and overwrite data/
# Chown-R mysql/usr/local/src/mysql/data/
# Cd/usr/local/src/mysql/
# Chown-R root: mysql.
Restart mysql of server B: # bin/mysqladmin-u root shutdown # bin/mysql-safe-user = mysql server-id = 2 & (you must specify the server-id. cnf)
Show slave status to check the synchronization configuration.
# Bin/mysql-u root
Master.info will appear in the/usr/local/src/mysq/data/Directory. this file records the information of the Master MySQL server. if you want to modify the slave configuration, delete the file first. otherwise, the modified configuration does not take effect.
Status test:
1. when both A and B networks and services are normal, the data changes from A side to B side to check for any data changes.
2. simulate B as A machine, or B may not always need to be connected to. after the data changed on the side is viewed on the B side, point B should have no data changes.
Dual-host mutual standby mode
If you add slave settings to aand master settings to B, you can synchronize data between B and.
Add the following settings to the mysqld configuration item in the configuration file of:
Master-host = 10.10.10.53
Master-user = backup
Masters-password = 1234
Replicate-do-db = backup
Master-connect-retry = 10
Add the following settings to the mysqld configuration item in the configuration file of B:
Log-bin = c: \ mysqllog
Binlog-do-db = backup
Note: *. err log file is generated when an error occurs. The synchronization thread exits. When an error is corrected, the synchronization mechanism should be executed to run slave start.
Restart the AB machine to implement two-way hot standby.
Test:
Insert A large data table AA (1872000) into database B in batches. Database A can update 2500 data entries per second.
