Master-slave synchronization settings for MySQL on the same machine

1. Configure the master database my. ini
Port = 3306
Datadir = "C:/Program Files/MySQL Server 5.0/Data /"
Server-id = 1
Log-bin = mysql-bin.log
 
2. Configure slave database my2.ini
Port = 3307
Datadir = "C:/Program Files/MySQL Server 5.0/Data2 /"
Server-id = 2
# Enable slave database logs for chained Replication
Log-slave-updates
# Whether the slave database is read-only. 0 indicates read/write, and 1 indicates read-only.
Read-only = 1
# Copy only a table
# Replicate-do-table = tablename
# Copy only some tables (matching characters available)
# Replicate-wild-do-table = tablename %
# Only copy a database (if you synchronize multiple databases, you can use multiple rows to represent them .)
Replicate-do-db = backup
# Copy only some databases
# Replicte-wild-do-db = dbname %
# Do not copy a table
# Replicate-ignore-table = tablename
# Do not copy some tables
# Replicate-wild-ignore-table = tablename %
# Do not copy a database (if synchronization of multiple databases is ignored, it can be represented in multiple rows .)
Replicate-ignore-db = mysql
# Whether the copied SQL statement is cleared immediately from the relay log. 1 indicates clearing immediately
Relay-log-purge = 1
# Slave server host, used to generate slave database list for show slave hosts
Report-host = slave-1
# No matter what error occurs, the image processing continues.
Slave-skip-errors = all
# Write the log file to the hard disk once every n log writes (synchronize the log information once ). N = 1 is the safest practice, but the efficiency is the lowest.
# The default value is n = 0, which means that the operating system is responsible for synchronizing binary log files.
Sync_binlog = 1
 
3. Set the master database
Start the master database:
Mysqld-nt -- defaults-file = my. ini
Connect to the master database and create a copy user
C:/> mysql-uroot-ppassword-P3306
Mysql> grant replication slave on *. * to 'backup '@ 'localhost' identified by '123 ';
Query OK, 0 rows affected (0.00 sec)
 
Lock the table of the master database to back up data files and initialize the slave database.
Mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
Displays the status of the master database. Note that the current binary log file name and position are recorded.
Mysql> show master status;
+ ----------------------- + ----------- + --------------------- + ---------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ---------------------- + ------------ + --------------------- + ---------------------- +
| Mysql-bin.000001 | 98 | backup | mysql |
+ ---------------------- + ------------ + --------------------- + ------------------------ +
1 row in set (0.00 sec)
Package the content in C:/Program Files/MySQL Server 5.0/Data/to C:/Program Files/MySQL Server 5.0/Data2, initialize the slave database. Of course, you can also use mysqldump for initialization.
 
4. Set slave Database
Enable another cmd to start the slave database.
Mysqld-nt -- defaults-file = my2.ini
Connect to slave database for configuration
C:/> mysql-uroot-ppassword-P3307
Mysql> CHANGE MASTER
-> MASTER_HOST = 'localhost ',
-> MASTER_USER = 'backup ',
-> MASTER_PASSWORD = 'backup ',
-> MASTER_LOG_FILE = 'mysql-bin.000001 ',
-> MASTER_LOG_POS = 98;
Query OK, 0 rows affected (0.01 sec)
Note that master_log_file and master_log_pos are the results of the previous show master status.
Start the replication process
Mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
Now the configuration is complete, and the table lock is unlocked in the master database.
Mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)