Recently, I have solved some problems related to Mysql master-slave cluster in my work. In line with the attitude of making progress every week, I have recorded the complete process of setting up master-slave cluster for future reference.

Introduction to Mysql

MySQL is a relational database management system developed by MySQL AB, a Swedish company. It is currently a product of Oracle. MySQL is one of the most popular Relational Database Management systems, and one of the best RDBMS (Relational Database Management System) applications in WEB applications.

MySQL is a relational database management system that keeps data in different tables instead of putting all data in a large warehouse, which increases speed and flexibility.

The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software adopts the double licensing policy, which is divided into community edition and commercial edition. Due to its small size, fast speed and low total cost of ownership, especially the characteristics of open source, MySQL is generally selected as the website database for the development of small and medium-sized websites.

Because of the excellent performance of the community version, pairing PHP with Apache makes for a good development environment.

Second, preparation

If the operating system is Centos7, prepare the Mysql installation package 5.7.26 in advance:

Mysql 5.7.26 download address

The download steps are as follows:

Click the Download button and log in. After login, jump to the following page:

Click Download Now to Download and upload the installation package to the /home directory on the server.

Mysql primary/secondary cluster construction

3.1. Single machine construction

We need to create an instance on a single node in the following environment:

Decompress the installation package just uploaded to the server

$CD /home $tar -zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz $mv./mysql-5.7.26-linux-glibc2.12-x86_64/./mysqlCopy the code

Add users and user groups of the MySQL database

$ groupadd -r mysql
$ useradd -r -g mysql mysql
Copy the code

Changing directory Permissions

    $ chown -R mysql:mysql /home/mysql/
Copy the code

Create a soft connection to the /user/bin directory so we can use mysql commands directly

    $ ln -s  /home/mysql/bin/mysql /usr/bin
Copy the code

Initializing the database

    $ /home/mysql/bin/mysqld --initialize --user=mysql --basedir=/home/mysql --datadir=/home/mysql/data
Copy the code

After initialization, remember the printed content, which contains the initial login password (be sure to record the password, or later can not log in).

Creating an RSA Key

    $ /home/mysql/bin/mysql_ssl_rsa_setup --datadir=/home/mysql/data/
Copy the code

Wait a few seconds, as shown:

Set automatic startup upon startup

    $ cp /home/mysql/support-files/mysql.server /etc/init.d/mysqld
Copy the code

Modify basedir and datadir in /etc/init.d/mysqld

  

Add automatic startup after startup

$ chkconfig mysqld on
$ chkconfig --list mysqld
Copy the code

After successfully joining, as shown in the figure:

  

Add the mysql executable file path to the global configuration file

    $ vim /etc/profile
Copy the code

Add the following

#mysql
export MYSQL_HOME=/home/mysql
export PATH=$MYSQL_HOME/bin:$PATH
Copy the code

Save the modification to make the configuration file take effect

    $ source /etc/profile
Copy the code

CNF, create 3306.cnf in the /home/mysql.conf/directory, and change the file property to mysql:mysql

$ mkdir /home/mysql/conf/
$ cd /home/mysql/conf/
$ vim 3306.cnf
Copy the code

Add the following content to the configuration file

[mysqld] basedir = /home/mysql/ datadir=/home/mysql/data/ socket=/tmp/mysql.sock log-error=/home/mysql/data/error.log server-id=1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 Language = / home/mysql/share/English # set up to monitor open port/multiple instances must different port = 3306 # set running user user = # mysql set utf-8 Expire_logs_days =30 # Set max_connections=1000 # Set max_connections=1000 # Set Max wait time (s) wait_timeout=120 interactive_timeout=120 # # include all files from the config directory # ! includedir /etc/my.cnf.dCopy the code

Sock file must be in the/TMP directory; otherwise, an error message will be displayed indicating that the sock file cannot be found.

Change the owners of all subfiles of /home/mysql./ data/ and /home/mysql./ conf to the mysql user

    $ chown -R mysql:mysql /home/mysql/data/ /home/mysql/conf/
Copy the code

After the modification, start mysql

    $ /home/mysql/bin/mysqld --defaults-file=/home/mysql/conf/3306.cnf &
Copy the code

Wait a few seconds, after the completion of initialization using provide password to log in, if there is an error can go/home/mysql/data/error. The log to check the log information

    $ mysql -u root -p
Copy the code

After a successful login, see the following figure:

  

The first thing to do after login is to change the initial password. My new password is Openailab, you can customize it

$ alter user user() identified by "";
$ use mysql;
$ update user set authentication_string = password('openailab'), password_expired = 'N', password_last_changed = now() where user = 'root';
$ FLUSH PRIVILEGES;
Copy the code

If port 3306 is not open, open the port first

$ firewall-cmd --zone=public --add-port=3306/tcp --permanent
$ firewall-cmd --reload
Copy the code

Then we connect through Navcat tool, as shown in the figure:

  

The solution here is to change Host in the User table to %

$ mysql -u root -p
$ use mysql;
$ update user set Host = '%' where User = 'root';
$ FLUSH PRIVILEGES;
Copy the code

Then reconnect to test and the problem is resolved, as shown:

  

To this single machine set up complete (data backup method is not said here, I will be mentioned in the following).

3.2. Standalone master-slave construction

The main difference between single machine master and slave and single machine is the different configuration files, we based on the above single machine environment for transformation

First kill the standalone process

$ps - aux | grep mysql $a kill 9 processCopy the code

Delete everything in /home/mysql/data and create two folders for each instance

$ cd /home/mysql/data/
$ rm -rf *
$ mkdir 3306 3307
Copy the code

Modified the configuration file 3306. CNF, added the content about the Master node

$ vim /home/mysql/conf/3306.cnf
Copy the code

Modified as follows:

[mysqld] basedir = /home/mysql/ datadir=/home/mysql/data/3306/ socket=/tmp/3306.sock log-error=/home/mysql/data/3306/error.log server-id=1 # Disabling symbolic-links is recommended to prevent assorted Security risks -- - links = 0 language = / home/mysql/share/English # set up to monitor open port/multiple instances must different port = 3306 # set user = mysql user operation Expire_logs_days =30 # set max_connections=1000 # set utF-8 encoding character-set-server= utF8 # Log-bin =mysql-bin binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-ignore-db=sys # # include all files from the config directory # ! includedir /etc/my.cnf.dCopy the code

Then we create the configuration file for the 3307 instance in /home/mysql.conf /

    $ vim /home/mysql/conf/3307.cnf
Copy the code

Add the following:

[mysqld] basedir = /home/mysql/ datadir=/home/mysql/data/3307/ socket=/tmp/3307.sock log-error=/home/mysql/data/3307/error.log server-id=2 # Disabling symbolic-links is recommended to prevent assorted Security risks -- - links = 0 language = / home/mysql/share/English # set up to monitor open port/multiple instances must different port = 3307 # set user = mysql user operation Expire_logs_days =30 # set max_connections=1000 # set utF-8 encoding character-set-server= utF8 # Interactive_timeout =120 # Configure replicate-ignore-db=mysql from the database replicate-ignore-db=information_schema replicate-ignore-db=performance_schema replicate-ignore-db=sys master-info-repository=TABLE relay-log-info-repository=TABLE relay-log-recovery binlog-format=ROW read_only=1Copy the code

Then we initialize database instances 3306 and 3307

$ /home/mysql/bin/mysqld --defaults-file=/home/mysql/conf/3306.cnf --basedir=/home/mysql/ --user=mysql --initialize-insecure --explicit_defaults_for_timestamp
$ /home/mysql/bin/mysqld --defaults-file=/home/mysql/conf/3307.cnf --basedir=/home/mysql/ --user=mysql --initialize-insecure --explicit_defaults_for_timestamp
Copy the code

Start the two instances separately

$ /home/mysql/bin/mysqld_safe --defaults-file=/home/mysql/conf/3306.cnf &
$ /home/mysql/bin/mysqld_safe --defaults-file=/home/mysql/conf/3307.cnf &
Copy the code

Note, here is the command I use/home/mysql/bin/mysqld_safe, and before I use when building standalone is/home/mysql/bin/mysqld, the latter is the difference between the open a daemon process monitoring instance, when the mysql instance to hang, The daemon will restart the instance immediately, so the problem is that the mysql instance will never die, so if you want to kill the mysql instance, you need to kill mysqLD_safe before killing the mysql instance.

Verify that the instance is successfully started, as shown in the figure:

Mysql > modify the password and Host configuration. Mysql > modify the password and Host configuration.

$mysql -uroot -p -p3306-h127.0.0.1Copy the code

If the default password is not empty, you can query the default login password by using the following command:

    $ find / -name my.cnf
Copy the code

Conf file and find the location of the log file. After confirming the location, open the log file and find the default password (search the password keyword in the file). The default password is shown as follows:

After the login is successful, run the following command to modify the configuration:

$ use mysql;
$ update user set Host = '%' where User = 'root';
$ update user set authentication_string = password('openailab'), password_expired = 'N', password_last_changed = now() where user = 'root';
$ FLUSH PRIVILEGES;
Copy the code

After modification, use Navcat to connect to the database, as shown in the figure:

  

Now I create oas.cloud library in Master database, add user table, input data, as shown in the picture:

Data synchronization is complete, and the primary/secondary server is set up successfully.

3.3. Primary/secondary cluster construction

Start MySQL instances on both machines

IP system port MySQL version node
192.168.3.204 Centos7 3306 5.7.26 Master
192.168.3.204 Centos7 3307 5.7.26 Slave
192.168.3.211 Centos7 3306 5.7.26 Master
192.168.3.211 Centos7 3307 5.7.26 Slave

Mysql -cluster can be used as a primary/secondary cluster.

3.4. Data backup

Data backup is very important, we must pay attention to it, here we use the Linux scheduled task to do data backup, has written a related article, portal:

Centos7 set scheduled task interested students can directly search

Run the following command to back up SQL files

    $ /home/mysql/bin/mysqldump -u root -p openailab -P 3306 --all-databases | gzip > ./backupfile_$(date +%Y%m%d%H%M%S).sql.gz
Copy the code

Only the backup files generated in the last 7 days are saved. The complete content of the script is as follows:

#! /bin/bash
t=$(date +%Y%m%d%H%M%S)
/home/mysql/bin/mysqldump -u root -p openailab -P 3306 --all-databases | gzip > /home/backup/mysql/data/backupfile_$t.sql.gz
find /home/backup/mysql/data -mtime 7 -type f|xargs rm -f
Copy the code

The data backup configuration is complete.