Shulou(Shulou.com)06/02 Report--

Mysql how to configure master-slave replication, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

1. Download the mysql installation package (I used the unzipped version of mysql-5.7) and unzip it into two copies named MySQLmaster and MySQLslave

two。 Modify the my.ini file of master

[mysqld] # Port port = 3306basedir=D:/hlb/ruanjian/mysql5.7_master/mysql-5.7.25-winx64datadir=D:/hlb/ruanjian/mysql5.7_master/mysql-5.7.25-winx64/data max_connections=200character-set-server=utf8default-storage-engine=INNODBsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES# databases that need to be backed up # binlog-ignore-db=mysql # Note: bin-do-db,bin-ignore-db is mutually exclusive You only need to set one of these items to # enable the binary logging log-bin=mysql-bin# service idserver-id=1 [mysql] default-character-set=utf8

3. Modify the my.ini file of slave

[mysqld] port = 3307basedir=D:/hlb/ruanjian/mysql5.7_slave/mysql-5.7.25-winx64datadir=D:/hlb/ruanjian/mysql5.7_slave/mysql-5.7.25-winx64/data max_connections=200character-set-server=utf8default-storage-engine=INNODBsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES# server idserver_id = enable binary log (it is not necessary to open binary log from the server) log-bin=mysql-bin [mysql] default-character-set=utf8

Installation of 4.mysql decompressed version

) Open cmd.exe as administrator) 2.cd D:\ hlb\ ruanjian\ mysql5.7_slave\ mysql-5.7.25-winx64\ bin) 3.mysqld-install mysqlmaster (or mysqlslave) 4.mysqld-- initialize-insecure-- user=mysql (this statement is used to initialize MySQL and generate the data directory) net start mysql starts MySQL command net stop mysql stops MySQL command mysql- uroot-P3306 (or-P3307) enters the mySQL command line

5. Configure master Mysql.

Binlog-do-db (the my.ini file that modified master earlier has this parameter) is used to specify the databases that need to be synchronized, and binlog-ignore-db specifies the databases that do not need to be synchronized. If neither of these parameters is set, the slave server replicates all the databases of the master server.

Generally, the root account is not used as the synchronization account. To do this, we need to create a new user on the main server (such as user01, password is 123456).

Here we create it on the command line as follows:

) Create a new user: create user 'user01'@'127.0.0.1' identified by' 123456clients; (the ip address after @ is the ip address of the client that is allowed to connect. ) 2. Configure the new user with master-slave replication permission: grant replication slave on *. * to 'user01'@'127.0.0.1' identified by' 123456permission; (the ip address after @ is the ip address of the client that is allowed to connect, and if it is changed to'%', the client has no restriction on ip address) 3. Check the master status of the master server: mysql > show master status; (the information will be used from the configuration)

6. Configure slave Mysql

Connect to the MySQL slave server:

Mysql-uroot-p-P3307

Configure the parameters for replication:

Change master to master_host='127.0.0.1',master_user='user01',master_password='123456',master_port=3306,master_log_file='mysql-bin.000008',master_log_pos=498

Start the slave replication function from the server:

Start slave

View the slave status of the slave server:

Mysql > show slave status\ G

If the values of both Slave_IO_Running and Slave_SQL_Running are Yes, all configurations replicated by the master and slave have been successful, that is, the slave server can automatically synchronize with the database data of the master server.

Since then, whenever the data of the master server is updated (for example, a new table has been created in the test database or the data in the table has changed), the slave server will automatically be consistent with the master server.

But if someone deliberately changes the data of the slave server, the data in the master server will not be updated synchronously unless we set the two MySQL servers to be masters and slaves of each other.

The main MySQL test of insert into user values (1) is fine.

The effect of the screenshot is as follows:

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.