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

Mysql master-slave configuration

You can find the relevant theoretical knowledge online. Here, I mainly introduce the mysql master-slave configuration from the actual configuration. I have queried a lot of information and tested many times. Finally, I have sorted out the configuration steps that can successfully mysql the master-slave configuration.

Introduction and description of the environment

Because the test environment is tested on the windows server, the whole environment configuration is configured according to windows, and linux is about the same, so you can modify it accordingly. The two servers are newly installed systems, and the operation will be carried out step by step according to the standard.

Operating system of master library and slave library: win 2012 master library and slave library version: mysql-5.7.23-winx64 master library IP address: 192.168.18.82 IP address of slave library: 192.168.18.83 master and slave port: 3306 database installation database installation

Download the packet version mysql-5.7.23-winx64 on the mysql official website, then extract it to disk D, configure the environment variable path (; D:\ mysql-5.7.23-winx64\ bin), and install vcredist_x64 (like a xxx120.dll and so on). Master and slave databases are all the same.

Master database configuration

Create a my.ini file in the root directory of the main database D:\ mysql-5.7.23-winx64. Content:

[mysql] # set the mysql client default character set default-character-set=utf8 # here set the character set, and it took a while to report an error Now I have commented by default [mysqld] # set port 3306 port = 3306 # set mysql installation directory basedir=D:\ mysql-5.7.23-winx64 # set mysql database data storage directory datadir=D:\ mysql-5.7.23-winx64\ data # allow maximum number of connections max_connections=200 # the character set used by the server defaults to 8-bit encoded latin1 characters Set character-set-server=utf8 # the default storage engine default-storage-engine=INNODB default_password_lifetime=0 # # that will be used when creating new tables sets the server ID Must maintain uniqueness server-id=1 # # set databases that need to write bin logs, multiple databases need to set binlog-do-db=test## on multiple lines that do not need to write bin logs, and multiple databases need to set multiple rows to set # binlog-ignore-db=mysql#binlog-ignore-db=information_schema## Note: binlog-do-db,   binlog-ignore-db   are mutually exclusive, you only need to set one of them # # more note that the SQL of the binlog-do-db library cannot have statements or actions to operate the binlog-ignore-db library, otherwise an error will be reported # # the location of the log mode binlog-format=row## binary log files can be set, or the path can be set, or it can be stored in log-bin=D:/mysql-5.7.23-winx64/data/test-binlog at this time.

The above content only needs to change the test database in binlog-do-db=test and log-bin=D:/mysql-5.7.23-winx64/data/test-binlog to its own database name according to the data configured by itself, and the rest does not need to be changed.

Then open cmd and go to the bin directory of the main database to configure:

Mysqld-- initialize-- user=mysql-- console # initializes and creates the data folder; mysqld-- install # installs the database and checks for success; net start mysql # starts the mysql data Mysql-u root-p # enter the database

Set password=password ('root'); # change the password of root to rootCREATE USER' abc'@'192.168.18.83' IDENTIFIED BY '123456permissions; # create user abc with the password of 123456GRANT REPLICATION SLAVE ON *. * TO' abc'@'192.168.18.83';# assign permissions; flush privileges; # refresh permissions CREATE DATABASE test; # create test database show master status; # View master status and record binary file names

The configuration of the master library is complete. Leave it first and don't close it. The binary file name and POS port will be used in the slave library configuration later.

Configure from the database

Create a my.ini file in the root directory of the database D:\ mysql-5.7.23-winx64. Content:

[mysql] # set the mysql client default character set default-character-set=utf8 # here set the character set, and it took a while to report an error Now I have commented by default [mysqld] # set port 3306 port = 3306 # set mysql installation directory basedir=D:\ mysql-5.7.23-winx64 # set mysql database data storage directory datadir=D:\ mysql-5.7.23-winx64\ data # allow the maximum number of connections max_connections=200 # the character set used by the server defaults to 8-bit coded latin1 Character set character-set-server=utf8 # default storage engine default-storage-engine=INNODB default_password_lifetime=0 # # to use when creating new tables to set server ID Must maintain uniqueness server-id=2 # # set databases that need to write bin logs, multiple databases need multiple rows to set replicate-do-db=test # # set databases that do not need to write bin logs, and multiple databases need to set replicate-ignore-db=information_schema replicate-ignore-db=mysql replicate-ignore-db=performance_schema replicate-ignore-db=sys for multiple rows

The above content only needs to change the test database in replicate-do-db=test to its own database name according to the data configured by itself, and the rest does not need to be changed.

Then open cmd and go to the bin directory of the main database to configure:

Mysqld-- initialize-- user=mysql-- console # initializes and creates the data folder; mysqld-- install # installs the database and checks for success; net start mysql # starts the mysql data Mysql-u root-p # enter the database

Set password=password ('root') # change the password of root to rootCREATE DATABASE test # create test database change master to master_host='192.168.18.82', # main database address master_user='abc', # user master_password='123456', # password master_log_file='test-binlog.000002' # the name of the log generated by the master database is master_log_pos=1172 # log_pos start slave; generated by the master database # start the slave database show slave status\ G; # View the slave database status

When both Slave_IO_Running and Slave_SQL_Running are YES, the master-slave synchronization setting is successful. Then you can do some verification, such as inserting a piece of data in a table in the test database of the master master database, and checking whether there is any new data in the same data table in the test library of slave to verify whether the master-slave replication function is effective.

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.