Replication of MySQL Database Backup

Copy directory of MySQL database backup: 1. mySQL replication Overview 2. advantages and ideas of MySQL replication 3. master server settings in the Data Replication environment 4. slave server setting for Data Replication environment 5. create a replication account 6. obtain master server 2

Copy directory of MySQL database backup: 1. mySQL replication Overview 2. advantages and ideas of MySQL replication 3. master server settings in the Data Replication environment 4. slave server setting for Data Replication environment 5. create a replication account 6. obtain master server 2

3. master server settings in the Data Replication Environment

In the actual production environment, a large amount of data already exists in the database before we deploy data replication. Therefore, we create a test database and data table in advance, used to demonstrate how to synchronize and back up existing data.

[Root @ master ~] # Mysql-u root-pmysql> create database hr; mysql> use hr; mysql> create table employees (-> employee_id int not null AUTO_INCREMENT,-> name char (20) not null,-> e_mail varchar (50),-> primary key (employee_id); mysql> insert into employees values-> (1, 'Tom ', 'Tom @ example.com '),-> (2, 'Jerry', 'Jerry @ example.com '); mysql> exit

We need to enable the binary log on the master server and set the server number. The unique server number must be an integer ranging from 1 to-1 and be set according to your actual situation. To perform these settings, close the MySQL database, edit the my. cnf or my. ini file, and add the corresponding configuration options in the [mysqld] Setting section. For more information about installing MySQL software, see section 4.11.2.

[Root @ master ~] # Vim/etc/my. cnf

[Mysqld]

Log-bin = Jacob-bin # enable binary logs and set the prefix of binary log files

Server-id = 254 # Set the server number

[Root @ master ~] # Service mysqld restart

[Root @ master ~] # Service iptables stop

L note: you cannot use the skip-networking parameter option in the configuration file. Otherwise, the slave server cannot connect to the master server and copy data.

4. slave server settings in the Data Replication Environment

If the slave server ID is not set, or the server ID conflicts with the master server, you must disable the MySQL service, re-edit the configuration file, set a unique server ID, and restart the MySQL service. If multiple slave servers exist, all server IDs must be unique. You can associate the server ID with the server IP address so that the ID number can uniquely identify a server computer. For example, you can use the last IP address as the MySQL server ID number.

[Root @ slave1 ~] # Vim/etc/my. cnf

[Mysqld]

Server-id = 2

[Root @ slave1 ~] # Service mysqld restart

[Root @ slave1 ~] # Service iptables stop

For replication, the binary log function on the MySQL slave server does not need to be enabled. However, you can also enable the binary log function of the slave server to back up and restore data. In addition, in some more complex topology environments, the MySQL slave server can also act as the master server of other slave servers.

5. Create a replication account

During data replication, all slave servers need to use their accounts and passwords to connect to the MySQL master server. Therefore, there must be at least one user account and the corresponding password on the master server for connection from the slave server. This account must have the replication slave permission. You can create different accounts and passwords for different SLAVE servers, or use a unified account and password. MySQL can use the create user statement to CREATE a USER and the GRANT statement to GRANT permissions to the account. If the user is only used for database REPLICATION, the account only needs the replication slave permission. In the following example, an slave_cp account with the replication permission will be created on the MySQL master server. This account can connect to the master server from any host in the example.com domain with the password SlaveAdmin.

[Root @ master ~] # Mysql-u root-pmysql> create user 'slave _ cp' @ '% .example.com' identified by 'slaveadmin'; mysql> grant replication slave ON *. * TO 'slave _ cp' @ '% .example.com'; mysql> exit

6. Obtain the binary log information of the master server

Before performing master-slave data replication, we understand basic information about the binary log files of the master server, which must be used in slave server settings, this information includes the name of the binary file of the master server and the location of the current log record, so that the slave server can know where to start the replication operation. You can use the following operations to view the binary log data of the master server:

[Root @ master ~] # Mysql-u root-pmysql> flush tables with read lock; mysql> show master status; + pipeline + ------------ + pipeline + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | + pipeline + ---------- + pipeline + | jacob-log.000001 | 1276 | | + -------------------------------- + ------------ + --------------------- + ---------------------------- + --------------------- + mysql> unlock tables;

The File column displays the binary log File name, and Position is the current log record location.

The flush tables with read lock command is used to perform READ-only locks on all TABLES in all databases. After READ-only locks, write operations on all databases are denied, but READ operations can continue. Executing the lock operation can prevent users from modifying the data while viewing the binary log information. Finally, the unlock tables statement is used to stop the global Lock operation.

7. Snapshot Backup for existing databases

If a large amount of data resources exist in the MySQL database system before using binary logs for data replication, you can back up the data using the mysqldump tool, use this tool on the master server to back up data and then restore the data on the slave server. When you want the data to be consistent between the master and slave nodes, you can use the data replication function to automatically synchronize the data. The specific operations are as follows (in the author's environment, the master server IP address is 172.16.0.254 and the slave server IP address is 172.16.0.1). In the actual production environment, you need to back up and restore the database as needed:

[Root @ master ~] # Mysqldump -- all-databases -- lock-all-tables>/tmp/dbdump. SQL [root @ master ~] # Scp/tmp/dbdump. SQL 172.16.0.1:/tmp/[root @ slave1 ~] # Mysql-u root-p 8. Configure the slave server to connect to the master server for Data Replication