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.
 
 
 
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

1 2 3 Next Page