Windows Server 2012 MySql master-slave architecture implementation

A master from

  1. Environment to prepare

    Windows Server One (preferably two) mysql 8.0.16 installation package (mysql-8.0.16-winx64.zip), configure the Windows environment, if the primary and secondary nodes are not on the same server, Notice the firewall, clock service synchronization of each node, and communication between nodes through host names.

  2. Ready to step

    1. Mysql installation, the master node, and extract the installation package to a specified directory: D: \ soft \ mysql – 8.0.16 – winx64, I am into here, after decompression is the lack of data directory and my ini configuration file, so we create. Also add the decompressed bin path to the environment variable path

      Create my.ini file for master;

      [mysqld]
Set port 3306
port=3306
Set mysql installation directory
Change according to your own address
basedir=D:\\soft\\mysql-8.0.16-winx64
Mysql > select * from 'mysql'
Change according to your own address
datadir=D:\\soft\\mysql-8.0.16-winx64\\data
# Maximum number of connections allowed
max_connections=2000
The number of connection failures allowed. This is to prevent someone from the host from trying to attack the database system
max_connect_errors=10
The default character set used by the server is UTF8
character-set-server=UTF8MB4
The default storage engine to use when creating new tables
default-storage-engine=INNODB
The mysql_native_password plugin is used by default
default_authentication_plugin=mysql_native_password

[mysql]
Mysql client default character set
default-character-set=UTF8MB4

[client]
Set the default port used by mysql client to connect to server
port=3306
default-character-set=utf8
Copy the code

    2. Mysql installation

      In the mysql installation directory: D:\soft\mysql-8.0.16-winx64\bin, run CMD as administrator and run the following command:

      Mysqld -- defaults - file = D: \ soft \ mysql - 8.0.16 - winx64 \ my ini - the initialize - the consoleCopy the code

      When the execution is complete, the initial default password of user root is printed, copied and saved.

      Mysql > install mysql

      Mysqld --install service name --defaults-file=D:\soft\mysql-8.0.16-winx64\my.iniCopy the code

      After the installation is successful, run the net start service name command to start the mysql service. Log in to the mysql server using the default password of user root and change the password.

      SQL > change password

      ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'New password';
Copy the code

      Install mysql and you are done. For details about how to install nodes from secondary nodes, see the preceding steps.

  3. Implementation steps

    • Architecture diagram

    • Configure the master primary server

      • Ini file in mysql installation directory, do some configuration, report binary log file, unique server-ID specified. Such as:

        [mysqld].#### Primary/secondary configuration ####
server-id=1
Mysql > open Mysql log file in binary format
log-bin=master-bin
Each write is synchronized with the hard disk and adjusted accordingly according to business requirements
sync-binlog=1
The binary database name to synchronize
binlog-do-db=xxxx
Only 7 days of binary logs are reserved in case the disk is full of logs. Adjust the logs according to service requirements
expire-logs-days=7
# Database not backed up
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql

There are three main types of mysql replication:
# Adjust accordingly to business needs
binlog_format=ROW
Copy the code

        After the modification, restart the mysqL service.

    • Creating a Replication Account

      • Create a special master/slave synchronization account on the master node. Each slave connects to the master using the standard MySQL username and password. The user performing the REPLICATION operation is granted REPLICATION SLAVE.

        # create accountCREATE USER `m-slave`@`%` IDENTIFIED WITH mysql_native_password BY 'password'PASSWORD EXPIRE NEVER; # authorizationGRANT Replication Client, Replication Slave ON *.* TO `m-slave`@`%`;
Copy the code

    • Checking master Status

      • Run the show Master status command on the Master database to check the binary log status and location of the primary server.

        Later, it’s used for synchronization.

    • Configure the slave server

      • Go to the my.ini file in the mysql installation directory of the secondary node, open the relay log, specify a unique Servr ID, and set the read-only permission. Add the following values to the configuration file:

        #### Primary/secondary configuration #####
# configure the secondary server with unique id
server-id=2
Open Mysql relay log, log format is binary
relay_log=mysql-relay-bin
Set read-only permission
read_only=1
Enable secondary binary logging
log_bin=slave-bin
Write the updated data to the binary log
log_slave_updates=1

The name of the library to be synchronized is the same
#replicate-do-db=xxx
#replicate-do-db=shop_ds_master

If the database name to be synchronized is different
Master = shop_ds_master; slave = shop_ds_slave
replicate-rewrite-db=test->test


# Skip all errors and continue the replication operation, suitable for large data synchronization
#slave-skip-errors=all

Mysql > alter database sync
replicate-ignore-db=mysql
Copy the code

        After the modification, restart the mysql service.

    • Start the replication thread from the server

      • MariaDB [(none)]> change master to master_host='192.168.37.111'.//IP address of the primary node>master_user='slave'.//Synchronous account>master_password='keer'.//password>master_port='3306'.//port>master_log_file='master-bin.000001'.//Bin of master-The log file>master_log_pos=1496; //Synchronous position numberCopy the code

      • Start replication thread:

        > start slave;
Copy the code

    • View the status of the secondary server

      • You can use SHOW SLAVE STATUS\G; Check the status of the slave server, as shown below, or show processList \G; To view the current replication status:

      •     Slave_IO_Running: Yes		// The IO thread is running properly
	Slave_SQL_Running: Yes		// The SQL thread is running properly
Copy the code

    • test

      Master/slave synchronization test:

      We create a database on the master server, use that database to create a table, add a record, and see if the slave server has been successfully synchronized. First, let’s see what databases are on both servers:

      ​ master:

      slave:

      Create a table t_user in the test library on the master server and see if this table exists on the slave server:

      This table is also available from the server: t_user

      The synchronization succeeds!