MySQL master-slave replication single or multi-table

The installation of the MySQL database is described below: the versions of the two databases must be consistent. Master Database: 192.168.0.43 slave Database: 192.168.0.53 modify 43 master data

The installation of the MySQL database is described below: the versions of the two databases must be consistent. Master Database: 192.168.0.43 slave Database: 192.168.0.53 modify 43 master data

The installation of the MySQL database is described below: the versions of the two databases must be consistent.



Primary Database: 192.168.0.43

Slave Database: 192.168.0.53

Modify the configuration file of the primary database:

Open the logbin log generation file:

# Binary logging-not required for slaves, but recommended

Log-bin = mysql-bin

# Binary logging format-mixed recommended

Binlog_format = mixed

Server-id = Master_id select the nf item, where master_id must be a positive integer between 1 and 232-1. Remove the preceding watching symbol

Server-id = 1

Restart the database service.

Configuration replication:

1. Create an account dedicated for replication to allow remote login:

Mysql> grant file, SELECT, replication slave on *. * TO 'slavuser' @ '192. 168.0.53 'identified by 'Password ';

Mysql> flush privileges;

2. For the current binary log name and offset value on the MASTER server, directly enter the command show master status.

Mysql> show master status;

+ ------------------ + ----------- + -------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ----------- + -------------- + ------------------ +

| Mysql-bin.000017 | 351051132 |

+ ------------------ + ----------- + -------------- + ------------------ +

1 row in set (0.00 sec)



The File column shows the log name, while the Position column shows the offset. In this example, the binary log value is a mysql-bin.000017 offset of 351051132. Record this value. These values will be used for setting slave servers later. They represent the replication coordinates, which tell the slave server to update from the master server.

After obtaining the snapshot and recording the log name and offset, you can re-enable the Write activity on the master server:

In step 2, you do not need to use step 3 to ensure that the data offset nodes are consistent when your master database is not stopped.




3. Use mysqldump to back up the data of the master server and load the dump file to the slave server:

Root @ hr43> mysqldump -- master-data-u root-p hrsoft_share Table Name> table name. SQL



Grep 'mysql-bin' table name. SQL query binary log name and Offset Value

Copy the vip_company. SQL file to the slave data.

Modify 53 the database configuration file on the slave server:

Log-bin = mysql-bin # Start bin-log

Server-id = 2

Master-host = 192.168.0.43 # master database address

Master-user = 'slaveuser' # execute logon user

Master-password = 'Password' # execution password

Master-port = 3306 # default port

Report-host = net-app1

Master-connect-retry = 30 # If the slave server finds that the master server has been disconnected for 30 S

Log-bin

Expire_logs_days = 3 # retain log files for 3 days

Log-slave-updates

Replicate-do-table = hrsoft_share. table name # copy table name only

If you want to copy multiple tables, you only need to add them below (copy a few to add a few)

Replicate-do-table = hrsoft_share. table Name