Master-slave synchronization for mysql Databases

Database Backup scheme mysql database master-slave synchronization 1. master server (mysqlmaster) IP: 192.168.1.107 port 3306 slave server (mysqlslave) IP: 192.168.1.127 port 3306I. master server operation 1. about the configuration of the master server 1.1 set server-id and enable binlog parameters according to m

Database Backup scheme mysql database master-slave synchronization 1. master server (mysqlmaster) IP: 192.168.1.107 port 3306 slave server (mysqlslave) IP: 192.168.1.127 port 3306 I. master server operation 1. about the configuration of the master server 1.1 set server-id and enable binlog parameters according to m

Database backup solution

Master-slave synchronization for mysql Databases

1. experiment environment deployment

Master server (mysql master) IP Address: 192.168.1.107 port 3306

Slave server (mysql slave) IP: 192.168.1.127 port 3306

I.Operations on the master server

1. Configure the master server

1.1 set the server-id value and enable the binlog Parameter

According to mysql synchronization principle: the key factor is binlog log.

Edit the/etc/my. cnf configuration file, modify and add relevant parameters.

[Root @ localhost ~] # Vi/etc/my. cnf

[Mysqld]

Server-id = 1

Log-bin = mysql-bin

Note:

#. The preceding two parameters are placed under the [mysqld] module in my. cnf; otherwise, an error occurs;

#. Search for the relevant parameters in the my. cnf file and modify them as required. If the parameter does not exist, add the relevant parameters. Remember that the parameters cannot be repeated;

#. Restart the database after modifying my. cnf Configuration

Command:/etc/init. d/mysqld restart,

After modifying the configuration file, check the configuration result:

[Root @ localhost ~] # Grep-E "server-id | log-bin"/etc/my. cnf

Restart mysql database

[Root @ localhost ~] #/Etc/init. d/mysqld restart

1.2 create an account rep for Master/Slave Data Synchronization

[Root @ localhost ~] # Mysql-uroot-pmyrootpw

Mysql> selectuser ();

Mysql> grantreplication slave on *. * to rep@192.168.1. % identified by '20140901 ';

Note:

# Replication slave: A required permission for mysql synchronization. Do not authorize all

# *. *: Indicates all tables in all databases. The database can also specify a specific database and table for replication, such as test. test1 (test1 table of the test database );

# Binlog-do-db = test
Binlog-ignore-db = mysql: a database that does not need to be backed up. Multiple write lines

# Rep@192.168.1. %: rep is the synchronization account, 192.168.1.% is the authorized host, % is used to allow the entire 192.168.1.0 network segment to be accessed by the rep user;

# Identified by "123456": 123456 is the password, and a complex password is used in the actual environment.

View User Permissions

Mysql> showgrants forrep@192.168.1.127;

1.3 read-only master database lock tables:

Note:In the actual environment, to operate master-slave replication, you need to apply for downtime, and the lock table will affect the business.

Mysql> flush tables with read lock;

Note:The lock table Command time is controlled by the following parameters in different engines. If the lock table exceeds the set time, it is automatically unlocked;

Default length:

Mysql> show variables like "% timeout %"; you can view the maximum time of the default lock table.

Test whether the lock table is successful after completion: If you open another window to create a table named test1, it will not be executed. It proves that the lock table cannot be updated, but can be readable and cannot be written, because it is a read lock, the lock table is mainly used to export database files, so as to obtain the correct offset value and ensure data consistency during import from the database.

1.4 view the status of the master database

View the status of the master database, that is, the current log file name and binary log offset.

Mysql> show master status;

The information displayed by the command must be recorded. The subsequent slave Database Replication starts from this location.

1.5 export master database data

[Root @ localhost ~] # Mkdir backup

[Root @ localhost ~] # Mysqldump-uroot-pmyrootpw-A-B | gzip> backup/mysql_bak. $ (date must have f1_ SQL .gz

Note:-A indicates backing up all databases, and-B indicates adding parameters such as user DB and drop (the database will directly overwrite all parameters during Database Export ).

[Root @ localhost backup] # ll

To ensure that no data is inserted into the database during the Database Import process, you can check the status information of the master database.

[Root @ localhost backup] # mysql-uroot-pmyrootpw-e "show masterstatus"

Note:In no special case, the binlog file and its location remain unchanged.

After importing the database, unlock the master database and restore the database to write;

Mysql> unlock tables;

Note: Some readers may be confused about how much data is actually updated from the master database, the slave database will soon catch up with the master database position progress from the show master status position above.

1.6 migrate the mysql DATA backed up in the master database to the slave Database

[Root @ localhost ~] # Scp backup/mysql_bak.2012-07-09. SQL .gzroot@192.168.1.127:/backup

II.Slave server operations

1. slave server configuration

1.1 set the server-id value and disable binlog settings

Note:The server-id of the database is unique within the LAN. The server-id here must be different from the master database and other slave databases, and comment out the binlog parameter configuration of the slave database;

Edit the/etc/my. cnf configuration file and modify relevant parameter settings.

Master-connect-retry = 60 # Time Difference Between the reconnection if the master server is disconnected from the server

[Root @ localhost ~] # Vi/etc/my. cnf

[Mysqld]

Server-id = 2

# Log-bin = mysql-bin

Check the configuration result

[Root @ localhost ~] # Grep-E "server-id | log-bin"/etc/my. cnf

Restart slave Database

[Root @ localhost ~] #/Etc/init. d/mysqld restart

1.2 restore data exported from the master database to the slave Database

Decompress the data backed up by the master database

[Root @ localhost backup] # ls

Restore the data extracted from the master database to the slave Database

[Root @ localhost backup] # mysql-uroot-pmyrootpw

1.3 log on to the slave database and configure synchronization Parameters

Mysql> change master to # connect to the master database

Mysql> master_host = "192.168.1.107", # IP address of the master database

Mysql> master_port = 3306, # The master database port. The slave database port can be different from the master database port.

Mysql> master_user = "rep", # The user rep created on the master database for Data Synchronization

Mysql> master_password = "123456", # the user's "rep" password mysql> master_log_file = "mysql-bin.000003", # is the name of the binary log file seen when mysql> showmaster status, it cannot contain multiple spaces. Mysql> master_log_pos = 376213; # It is the binary log offset viewed when mysql> show master status, and cannot contain multiple spaces.

1.4 enable slave Database Synchronization

Enable the slave Database Synchronization switch and view the synchronization status

[Root @ localhost backup] # mysql-uroot-pmyrootpw-e "start slave"

[Root @ localhost backup] # mysql-uroot-pmyrootpw-e "show slave status \ G"

You can also log on to the slave database and run the following commands in the database:

Mysql> start slave;

Mysql> show slave status \ G;

Check whether the build is successful. Check whether the IO and SQL threads are in the "yes" status.

Slave_to_Running: YES # reads binlog logs from the master database and writes them to the slave database relay logs.

Slave_ SQL _Running: YES # reads and executes the binlog conversion SQL statement in the relay log and then applies it to the database for summary.

You can also execute the command to filter and view the information as follows:

[Root @ localhost backup] # mysql-uroot-pmyrootpw-e "show slave status \ G" | egrep "IO_Running | SQL _Running"

1.5 test master-slave Synchronization

Create a database in the master database and view the database.

Create a database named "mytable" in the master database for master-slave synchronization:

[Root @ localhost] # mysql-uroot-pmyrootpw-e "show databases;

[Root @ localhost] # mysql-uroot-pmyrootpw-e "create database mytable ;"

Check whether master-slave synchronization is performed in the slave database:

[Root @ localhost] # mysql-uroot-pmyrootpw-e "show databases;

Here! The master-slave Database Synchronization is completed successfully, and the slave database can synchronize data.