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

In this issue, Xiaobian will bring you about how to implement MySQL database mirroring in Linux. The article is rich in content and analyzed and described from a professional perspective. After reading this article, I hope you can gain something.

MySQL provides database replication function after version 3.23.15, which can realize the synchronization of two databases, master-slave mode and mutual backup mode. Realize synchronous backup of data.

The settings for database replication are reflected in the mysql settings file. mysql configuration file (usually my.cnf) in Linux environment in/etc/my.cnf or mysql user's home directory under my.cnf.

Setting method:

OS: Red Hat Linux 9 mysql: mysql-standard-4.0.18-pc-linux-i686.tar.gz

A Server: 192.168.0.1 Master Server

B Server: 192.168.0.10 slave

MySQL has been installed on A and the database is already available. Install mysql-standard-4.0.18 on B. Install mysql according to INSTALL-BINARY method after unpacking. I will then start mysql.

A Server Settings

#mysql -u root -p Master opens an account dbbackup password 123456 to IP:192.168.0.10 with file processing permissions. mysql>GRANT ALL ON *.* TO dbbackup@192.168.0.10 IDENTIFIED BY'123456' mysql>exit

#mysqladmin -u root -p shutdown

Backup Master All databases.. The tar command is usually used. (Note: When tar,MySQL is to be in the stop case)

#tar zcvf /tmp/mysql.tar.gz /usr/local/src/mysql/data

Modify/etc/my.cnf on machine A

[mysqld]

log-bin (Generate.index file.# Set log to be logged You can set log-bin=c:mysqlbakmysqllog to set the directory of log files,

#where 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 (#specifies database requiring logs as forimage)

Start server A mysql: Bin/mysqld-safe -user=mysql server-id=1 & (server-id to specify)

At this point, due to the log-bin parameter, index files are generated, and there is a.index file in the/usr/local/src/mysql/data directory. Log of database transactions. #mysql -u root -p Use the show master status command to view logs.

B Server Settings

Set/etc/my.cnf

[mysqld]

master-host=192.168.0.1

master-user=dbbackup (#sync user account)

master-password=123456

master-port=3306

server-id=2

master-connect-retry=60 (default retry interval is 60 seconds)

replicate-do-db=forimage (update database forimage only)

log-slave-updates

copy/tmp/mysql.tar.gz on A to MySQL installation directory on B under data/and extract overwrite data/

#chown -R mysql /usr/local/src/mysql/data/

#cd /usr/local/src/mysql/

#chown -R root:mysql .

Restart mysql on server B: #bin/mysqladmin -u root shutdown #bin/mysql-safe -user=mysql server-id=2 & (to specify server-id, correspond to my.cnf)

show slave status View synchronization configuration.

#bin/mysql -u root

Then master.info will appear in/usr/local/src/mysq/data/directory, this file records Master MySQL server information. If you want to modify the relevant slave configuration to delete the file. Otherwise, the modified configuration will not take effect.

Condition test:

1. A and B network and services are normal, from A end after the change of data, to B end browse data, check whether there is data change.

2. Simulate B crashes, or B doesn't have to be connected to A all the time. After changing the data from end A, browse the data from end B, and point B should have no data change.

Dual standby mode

If you add slave settings to A and master settings to B, you can synchronize B->A.

Add the following settings to the mysqld configuration item in A's configuration file:

master-host=10.10.10.53

master-user=backup

master-password=1234

replicate-do-db=backup

master-connect-retry=10

Add the following settings to the mysqld configuration item in B's configuration file:

log-bin=c:mysqllogmysqllog

binlog-do-db=backup

Note: When an error occurs *.err log file. Synchronous thread exit, when the error is corrected to make the synchronization mechanism work, run slave start

Restart AB machine, you can achieve two-way hot standby.

Testing:

Insert AA (1872000) pieces of large data into B in batches, and A database can update 2500 pieces of data per second.

The above is how to achieve MySQL database mirroring in Linux shared by everyone. If there is a similar doubt, please refer to the above analysis for understanding. If you want to know more about it, please pay attention to the industry information channel.

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.