Shulou(Shulou.com)05/31 Report--

How to set up master-slave synchronization in MySQL? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. Configure the main library my.ini

Port=3306

Datadir= "C:/Program Files/MySQL/MySQL Server 5.0/Data/"

Server-id=1

Log-bin=mysql-bin.log

2. Configure slave library my2.ini

Port=3307

Datadir= "C:/Program Files/MySQL/MySQL Server 5.0/Data2/"

Server-id=2

# enable slave log so that chain replication can be carried out

Log-slave-updates

# whether the slave library is read-only. 0 means read-write, 1 means read-only.

Read-only=1

# copy only a table

# replicate-do-table=tablename

# copy only certain tables (matches are available)

# replicate-wild-do-table=tablename%

# copy only a library (if you synchronize multiple databases, it can be represented by multiple rows. )

Replicate-do-db = backup

# copy only certain libraries

# 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 library (if the synchronization of multiple databases is ignored, it can be represented by multiple lines. )

Replicate-ignore-db=mysql

# whether the copied sql statements are cleared from the relay log immediately. 1 means to clear immediately.

Relay-log-purge = 1

# from the server host, used for show slave hosts to generate slave library list

Report-host=slave-1

# that is, no matter what error occurs, the mirroring processing continues

Slave-skip-errors=all

# write the log file to the hard disk once after n log writes (synchronize the log information). Number1 is the safest approach, but the least efficient.

# the default setting is nroom0, which means that the operating system is responsible for the synchronization of binary log files.

Sync_binlog=1

3. Set up the main library

Start the main library:

Mysqld-nt-defaults-file=my.ini

Connect to the main library and create a replication user

Mysql-uroot-ppassword-P3306

Mysql > grant replication slave on *. * to identified by '123456'

Query OK, 0 rows affected (0.00 sec)

Lock the table of the master library so that the backup data files can be initialized to the slave library

> flush tables with read lock

Query OK, 0 rows affected (0.00 sec)

Show the status of the main library and note the current binary log file name and position

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 and copy the contents under C:/Program Files/MySQL/MySQL Server 5.0/Data/ to C:/Program Files/MySQL/MySQL Server 5.0/Data2/, and initialize the slave library. Of course, initialization can also be done using mysqldump.

4. Set up slave library

Open another cmd to start the slave library

Mysqld-nt-defaults-file=my2.ini

Connect to the slave library for configuration

Cvv / > mysql-uroot-ppassword-P3307

Mysql > CHANGE MASTER TO

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

Notice 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)

At this point, the configuration is basically complete. Unlock the table in the main library.

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

After reading the above, have you mastered how to set master-slave synchronization in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.