Introduction of MySQL Master and slave setup method

Master MySQL Settings

1, edit the main MySQL server MySQL configuration file my.cnf, under [mysqld] Add the following parameters:

Log-bin=mysql-bin//open MySQL binary log

Server-id=1//server ID cannot be duplicated

BINLOG-DO-DB=DZX2//need to decide the name of the database from the backup

Expire-logs-days = 7///Only 7-day binary log is maintained to prevent disk from being filled with log

2, in a server to add a master-slave copy of the account:

Login to MySQL command line, execute

GRANT REPLICATION SLAVE on *.* to ' account ' @ ' from server IP ' identified by ' password ';

For example: The account number is rep, the password is 123,ip is 192.168.1.3, then executes the code as

GRANT REPLICATION SLAVE on *.* to ' rep ' @ ' 192.168.1.3′identified by ' 123′;

3, restart MySQL, so that the configuration effective

Close forum access, copy database backup to from library server

1, Login forum backstage, "Global-site information", Closed forum visit

2, login to the MySQL command line, such as: mysql-uroot–p

3, on the main MySQL server to execute the command, the database set to read-only state:

FLUSH TABLES with READ LOCK;

4, execute the command, and note the value of file and position:

Show master status;

5, backup needs to master from the backup database, exported to SQL or directly copy the database file way can be

6, back to the MySQL command Line window, to solve the database read-only status, execution:

UNLOCK TABLES;

7, Login forum Backstage, "Global-site information", open forum visit

8. Copy the data that you just backed up to the library server

Set from MySQL server

1, edit the MySQL configuration file from the MySQL server my.cnf, add the following parameters under [mysqld]:

server-id=2//server ID cannot be duplicated

master-port=3306//Main Library port

REPLICATE-DO-DB=DZX2//database name to be replicated

Replicate-ignore-table=dzx2.pre_common_session//Automatically skipped table, the session table does not need to replicate

Slave-skip-errors = 1032,1062,126,1114,1146,1048,1396//automatically skipped error code to prevent replication errors from being interrupted

2. Restore the database backed up from the main library to the library

3, restart from the library MySQL

4, login from the library MySQL command line, execution:

Change Master to master_host= ' 192.168.1.2′, master_user= ' rep ', master_password= ' 123′ ', master_log_file= ' value of ' file ', The value of the master_log_pos=position;

Set connection information, file and position values are previously recorded, position values do not have single quotes, other values are single quotes

5. Implementation:

Start slave; Start Connection from library

6, view from the library status:

show slave statusg; Check the connection condition, whether two yes, two yes for success

7, edit the MySQL configuration file from the MySQL server my.cnf, add the following parameters under [mysqld]:

master-host=192.168.1.2//Main Library A's IP

Master-user=rep//accounts just created in the main library

master-password=123//password