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

Master-slave replication of Mysql

The master-slave replication of Mysql is generally used to ensure the synchronization of data. For example, if we have a branch, we need to synchronize the data to a branch thousands of miles away, which is very convenient and fast. In this experiment, we also implement ssl secure connection, which plays a role in protecting data in the process of synchronization.

The mysql version we used in this experiment is 5.7. it will be a little different from the previous version.

Mysql5.7 supports native systemd

Mysql5.7 has better optimization, better InnoDB storage engine and more robust replication capabilities for multicore CPU, solid state drives, and locks.

Install mysql:

The system environment I use: centos7.2x86_64, this version comes with mariadb-libs, so uninstall it:

Rpm-aq | grep mariadb

Install the dependency package:

Install cmake

[root@localhost ~] # tar zxf cmake-3.5.2.tar.gz

[root@localhost ~] # cd cmake-3.5.2/

[root@localhost cmake-3.5.2] #. / bootstrap

Compile using gmake & & gmakeinstall

[root@localhost cmake-3.5.2] # gmake & & gmake install

Install ncurses

[root@localhost ~] # tar zxf ncurses-5.9.tar.gz

[root@localhost ~] # cd ncurses-5.9/

[root@localhost ncurses-5.9] # / configure & & make & & make install

Install bison

[root@localhost ~] # tar zxf bison-3.0.4.tar.gz

[root@localhost ~] # cd bison-3.0.4/

[root@localhost bison-3.0.4] # / configure & & make & & make install

Install boost

[root@localhost ~] # tar zxf boost_1_59_0.tar.gz

[root@localhost ~] # cd boost_1_59_0/

[root@localhost boost_1_59_0] # cd

[root@localhost ~] # mv boost_1_59_0 / usr/local/boost

Create a user and add to a group

[root@localhost] # groupadd-r mysql & & useradd-r-g mysql-s / bin/false-M mysql

Create the mysql installation directory and database directory

Mkdir/usr/local/mysql-create a directory

Mkdir/usr/local/mysql/data-database directory

Compile and install mysql

We can use make & & make install to compile and install, but this command uses only a single thread, and the installation process is too slow. Using the following command, you can specify the number of threads at compile time according to the number of CPU cores, which greatly improves the speed of installation.

Make-j $(grep processor/proc/cpuinfo | wc-l)

-j: specify the number of threads at compile time based on the number of CPU cores

Input errors are inevitable when installing the required modules. When we want to rerun the cmake configuration, we need to delete the CMakeCache.txt file: # makeclean

# rm-f CMakeCache.txt

Optimize the execution path of Mysql

Set permissions and initialize the MySQL system authorization table

# cd / usr/local/mysql

# chown-R mysql:mysql. -change the owner, belong to the group, and note that it is mysql.

Note: the initial system database script is released after 5.7.6 (this article uses this method to initialize)

# / usr/local/mysql/bin/mysqld-initialize-insecure-user=mysql-basedir=/usr/local/mysql

-- datadir=/usr/local/mysql/data

Generate an initial password for mysql (save it), which will be used later!

Create a profile

# cd / usr/local/mysql/support-files-enter the MySQL installation directory for supporting files

# cp my-default.cnf / etc/my.cnf-copy the template to the new configuration file

Modify the configuration options in the file, as shown in the following figure, and add the following configuration items

# vi / etc/my.cnf

Configure mysql to start automatically

We will report an error when we open the mysql service, because in mysqld.service, the default pid file is specified to the / var/run/mysqld/ directory, but the directory is not established beforehand, so we have to manually set up the directory and assign permissions to the mysql user

There are two solutions:

One is to create the directory / var/run/mysqld specified in the mysqld.service file and give the master permission: chown-R mysql:mysql/var/run/mysqld

The second is to modify the specified path in the / usr/lib/system/system/mysqld.service file to write an existing path

Systemctl daemon-reload reload

Start the mysql service again

Connect to the database-the password is the initial password that you just opened

Change the password of the library administrator user root

Implementation of master-slave replication based on ssl secure connection

1) create a SSL/RSA file in the master MySQL

# cd / usr/local/mysql/bin-switch directories

# mysql_ssl_rsa_setup-user=mysql-basedir=/usr/local/mysql

-- datadir=/usr/local/mysql/data-create a new SSL file

Before restarting the mysqld service, we need to give server-key.pem permission

Chmod + r / usr/local/mysql/data/server-key.pem

Then restart the service # systemctl restart mysqld

Restart the mysql service, and there will be no error in the error log

Log in to mysql and execute mysql > showvariableslike'%ssl%'

As you can see from the figure above, mysql supports ssl secure connections

After the operation on the main mysql is completed, a copy account is generated: REQUIRESSL

Enter mysql:

Mysql-u root-p '123.abc'

Mysql > grant replication slave on *. * to 'rep'@'192.168.1.%'identified by require ssl

Enable binary logging and restart the mysql service on the primary mysql

Add the following configuration item

Log-bin=mysql-bin

Restart mysql

Systemctlrestart mysqld

Enter mysql

Mysql-u root-p '123.abc'

View master status

Showmaster status

Note: to remember the values of file and position shown in the figure above, configure the firewall to allow 3306/tcp communication from the server.

[root@localhost] # firewall-cmd-- permanent-- add-port=3306/tcp

[root@localhost ~] # firewall-cmd-reload

Configure slave server

# # Clone the master mysql and modify the ip.

From the / etc/my.cnf file contents of the mysql server

Basedir = / usr/local/mysql

Datadir = / usr/local/mysql/data

Port = 3306

Server_id = 2

Socket = / usr/local/mysql/mysql.sock

Log-error = / usr/local/mysql/data/mysqld.err

Relay-log = / usr/local/mysql/data/relay-log-bin

Relay-log-index = / usr/local//mysqldata/slave-relay-bin.index

Note: server_id should be unique and cannot be duplicated with other mysql hosts

Give the certificate generated by the master mysql to the slave server

Execute scp under the / usr/local/mysql/daata path in the main mysql

[root@localhost data] # scp ca.pem client-cert.pemclient-key.pem root@192.168.1.150:/usr/local/mysql/data/

Note: 192.168.1.150 is the slave mysql's ip,192.168.1.100 is the ip address of the master mysql.

! Set r permission for client-key.pem, otherwise the private key cannot be used

Restart the mysql service

Systemctl restrt mysqld

Access to the database

Mysql-u root-pendant 123.abc'

Check to see if SSL is supported

Show variables'% ssl%'

Requirement: have-openssl YES

Have-ssl YES

Try connecting to the master server with SSL on the slave mysql before configuring master-slave replication:

Cd / usr/local/mysql/data

Mysql-ssl-ca=ca.pem ssl-cert=client-cert.pem ssl-key=client-key.pem-u rep

-p123456-h 192.168.1.100

After entering the database, use\ s to view the database details, and you will find that the ip,ssl test connection of the primary mysql is successful.

Finally, configure the master-slave replicate, log in to the slave mysql, and execute on the slave mysql:

Enable slave mysql server

Note: because our slave mysql is the cloned master mysql, we need to modify the uuid of the slave mysql

The path is: / usr/local/mysql/daa/auto.cnf

Restart the mysqld service after modification is completed

Systemctl restart mysqld

Log in to execute from mysql

Show slave status\ G

You will find:

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

Test: create libraries and tables on the master mysql and synchronize immediately on the slave mysql!

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.