Two mysql instances are installed on one computer two days ago. One port is 3306 and the other is 3307.
Configure the two mysql Databases and test master and slave.
My test environment is windows, so you need to modify the my. ini file in the mysql installation directory.
First, configure the master.
Add the following code at the end of mysql my. ini file 3306.
Server-id = 1
Log-bin = c: mysqllog/panlogs
Binlog-do-db = panpan
Binlog-ignore-db =
Server-id = 1 is the master id. The id of slave is set to 2.
Log-bin = c: mysqllog/panlogs is the log file of the master, and the storage address and name.
Binlog-do-db = panpan indicates the database file to be synchronized. The name of the database to be synchronized is panpan. If multiple databases are backed up, set this option again.
Binlog-ignore-db = is a database that does not need to be backed up.
Set slave. Same as above. Open another mysql INI file and add the following at the end:
Server-id = 2 # (set the id number in sequence When configuring multiple slave servers)
Master-host = 127.0.0.1 # IP address of the master server (because I tested the host here, I entered 127.0.0.1)
Master-user = pan # user name authorized by the master server
Master-password = 123456 # password authorized by the master server
Master-port = 3306 # master server port
Replicate-do-db = panpan # Name of the database to be backed up. If multiple databases are backed up, set this option again.
Here, we have achieved success.
But there are several points to note:
1. Generally, we add unused user names for different databases and have different permissions. Therefore, when configuring slave, the master user must have the corresponding permissions. Use this statement to set: grant file, select, replication slave on *. * to 'pan' @ '2017. 0.0.1 'identified by '200 ′;
2. view the master and slave statuses.
In the command line, enter myslq and view the master status through show master status. Use show slave status; to view the status of slave.
Mysql> show master status;
+ -------- + ---- + ----- + ------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ -------- + ---- + ----- + ------ +
| Panlogs. 000001 | 98 | panpan |
+ -------- + ---- + ----- + ------ +
Show slave status;
If you see:
The values in the Slave_IO_Running and Slave_ SQL _Running columns are "Yes", indicating that the Slave I/O and SQL threads are running normally.
3. After the configuration is successful, restart two mysql instances. We recommend that you restart the master instance and then restart slave.
4. The master.info file is generated under the Data Directory of slave. We recommend that you delete this file before restarting. This is the information of the saved master. This is not updated during restart. Therefore, you need to delete the file and regenerate it.
5. You have not encountered any other problems. If you have encountered any problems, please leave a message ....
Pan shaoning's blog