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

Mariadb-5.5.52 master replication cluster installation step

one。 Because the website business requires very high consistency in data reading, the mariadb database must be in the form of master-slave replication cluster to meet the business requirements.

two。 An introduction to the process of replication configuration

There are two Mariadb database servers, db01 and db02,db01, and db02 is the slave server. in the initial state, the data information in db01 and db02 is exactly the same. when the data in db01 changes, db02 will change accordingly, making the data information of db01 and db02 synchronized, so as to achieve the purpose of backup.

Main points:

The medium responsible for transmitting various modification actions in the master and slave server is the binary change log of the master server, which records the various modification actions that need to be transmitted to the slave server.

Therefore, the primary server must activate the binary logging feature. The slave server must have sufficient permissions to connect to the master server and request the master server to transfer the binary change log to it.

The process of configuring master-slave replication:

1)。 Master node operation steps

(1) enable binary logging

(2) set a server-id that is unique in the current cluster

(3) create a replication slave,replication client account with replication permission

2). Operation steps of slave node

(1) enable relay log

(2) set a server-id that is unique in the current cluster

(3) use a user account with replication permission to connect to the primary server and start the replication thread

Note:

(1). Server version: the master and slave servers have the same version

If the version is inconsistent, you must ensure that the version of the slave server is higher than that of the master server.

(2) if the isolation level of the mariadb database is readable, its binary log format should be row-based.

Experimental environment:

The server version is:

CentOS 7.2 64bit

The version of the database software is:

Mariadb-5.5.52

The IP address of the db01 node: 172.16.22.8

The IP address of the db02 node: 172.16.22.6

III. Yum installs Mariadb database

Delete all MySQL/MariaDB-related rpm packages

MySQL is no longer included in the source of CentOS 7 and uses MariaDB instead

1. Use rpm-qa | grep mariadb to search for existing packages in MariaDB:

If it exists, use rpm-e-- nodeps mariadb-* to delete all

2. Use rpm-qa | grep mysql to search for existing packages in mysql:

If it exists, delete it all using yum remove mysql mysql-server mysql-libs compat-mysql

I recommend using the mariadb that comes with the system, so that you can install it directly with yum.

3. Yum install mariadb

[root@~] # yum install-y mariadb mariadb-server

# Note: mariadb is the database client and mariadb-server is the database server

4. Command to start MariaDB service

[root@~] # systemctl start mariadb

5. Check whether the mariadb process service is running

[root@~] # ps-ef | grep mariadb

Root 10131 10095 0 19:24 pts/2 00:00:00 grep-color=auto mariadb

Mysql 10197 10009 0 Jan19? 15:56:01 / usr/sbin/mysqld-basedir=/usr-datadir=/var/lib/mysql-plugin-dir=/usr/lib64/mysql/plugin-user=mysql-log-error=/var/log/mariadb/mariadb.log-pid-file=/var/lib/mysql/zgz.pid-socket=/var/lib/mysql/mysql.sock

6. View database status

[root@~] # systemctl status mariadb

7. Then run mysql_secure_installation to initialize the configuration MariaDB:

I think we can change the database password at this stage, except for Disallow root login remotely and Remove test database and accesss to it can be n, the other is y. It mainly depends on your actual needs.

8. Log in to MariaDB and create the corresponding database user and database

(1) Log in using mysql-uroot-p, and you will be prompted to enter your password after entering enter.

(2) create a new user, CREATE USER 'git'@'localhost' IDENTIFIED BY' $password';, where $password fill in the password set by yourself. Of course, it can also be modified later.

(3) set storage engine

Mariadb [none] > set storage_engine=INNODB

(4) create a database

Mariadb [none] > create database database_name character set utf8

(5) set user permissions

Grant all privileges on *. * to 'root'@'%' identified by' $password' with option

# the above can be defined as enabling root users to grant the highest permissions to any machine that can connect remotely, and to grant others the right to connect.

# Note: both master and slave database Mariadb can be installed according to the above installation method.

four。 Database master-slave replication

1. Primary node db01 configuration

Configure master, including opening the binary log and specifying a unique servr ID.

Vim / etc/my.cnf, add the following to the configuration section [mysqld]:

[mysqld]

# binary change Log

Log-bin=mysql-bin

# binary log format is mixed mode

Binlog_format=mixed

# ID value of the primary server db01

Server-id = 1

two。 Db02 configuration from node:

The configuration / etc/my.cnf adds the following content to the [mysqld] configuration segment:

Log-bin=mysql-bin

Binlog_format=mixed

Server-id = 12

Relay-log = relay-bin

Log_slave_updates = 1

Read_only = on

# Note:

Server_id is necessary and unique. It is not necessary for slave to turn on binary logging, but in some cases, you must set the

If slave is the master of another slave, bin_log must be set. Here, we turn on the binary log and display the name (the default name is hostname, but there will be a problem if the hostname changes).

Relay_log configures relay logs, and log_slave_updates indicates that slave writes replication events to its own binary log (you'll see its usefulness later).

Some people open the binary log of slave, but do not set log_slave_updates, and then check to see if the data of slave has changed, which is a misconfiguration. So try to use read_only, which prevents data from being changed (except for special threads). However, read_only is not very practical, especially for applications that need to create tables on slave.

3. Set up a backup account in the database of the Master node db01: each slave connects to master using the standard MySQL username and password. The user performing the replication operation is granted REPLICATION SLAVE permission. The password of the user name is stored in the text file master.info

The command is as follows:

[root@db01] # mysql-u root-p

# set up an account mysql_sync, and only allow 172.16.22.6 this IP to log in with the password mysql_sync.

MariaDB [(none)] > grant replication client,replication slave on *. * to 'mysql_sync'@'172.16.22.6' identified by' mysql_sync'

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)] > flush privileges

Query OK, 0 rows affected (0.00 sec)

4. Copy data

Shut down the Master server, copy the data in Master to the slave server node, synchronize the data in Master and slave, and make sure that write operations in Master and slave servers are prohibited before all setup operations are completed, so that the data in the two databases must be the same!

But if your two servers are completely newly installed MariaDB master and slave servers, this step is not necessary. Because the newly built database doesn't have much data.

5. Both master and slave nodes restart the mysqld service

Primary node:

[root@db01 ~] # systemctl restart mariadb

Slave node:

[root@db02 ~] # systemctl restart mariadb

6. Start the relay log of the slave node

The next step is to have slave connect to master and start redoing the events in the master binary log. Use the CHANGE MASTER TO statement instead of making changes to the configuration file, and it can specify a different master for slave without stopping the server.

The command is described as follows: you can view command options using help change master to

CHANGE MASTER TO option [, option]...

Option:

MASTER_BIND = 'interface_name'

| | MASTER_HOST = 'host_name' master server address |

| | MASTER_USER = 'user_name' user name with copy permission |

| | MASTER_PASSWORD = 'password' user password |

| | MASTER_PORT = the port of the port_num master server |

| | MASTER_CONNECT_RETRY = interval connection retry interval |

| | MASTER_HEARTBEAT_PERIOD = interval heartbeat detection interval |

| | MASTER_LOG_FILE = 'master_log_name' master server binary log file |

| | MASTER_LOG_POS = the location in the master_log_pos binary log file |

| | RELAY_LOG_FILE = 'relay_log_name' relay log file name definition |

| | RELAY_LOG_POS = relay_log_pos relay log time point definition |

| | MASTER_SSL = {0 | 1} all of the following are related to SSL secure transmission |

| | MASTER_SSL_CA = 'ca_file_name' |

| | MASTER_SSL_CAPATH = 'ca_directory_name' |

| | MASTER_SSL_CERT = 'cert_file_name' |

| | MASTER_SSL_KEY = 'key_file_name' |

| | MASTER_SSL_CIPHER = 'cipher_list' |

| | MASTER_SSL_VERIFY_SERVER_CERT = {0 | 1} |

| | IGNORE_SERVER_IDS = (server_id_list) |

The time point of the binary log file that needs to be recorded is the last point. Let's go to the master server node to check which time point. Because it is a new server, there is no important data on the master node:

MariaDB [(none)] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000007 | 426 |

+-+

1 row in set (0.00 sec)

Start the relay log for the slave node:

[root@db02] # mysql-uroot-p$password

# check whether read-only is open or not

MariaDB [(none)] > show global variables like'% read_only%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_read_only | OFF |

| | read_only | ON |

| | tx_read_only | OFF |

+-+ +

3 rows in set (0.01sec)

# the command to start the relay log is as follows

MariaDB [(none)] > change master to master_host='172.16.22.8',master_user='mysql_sync',master_password='mysql_sync',master_log_file='mysql-bin.000007',master_log_pos=426,master_connect_retry=5,master_heartbeat_period=2

Query OK, 0 rows affected (0.07 sec)

# View the status of slave server

MariaDB [(none)] > show slave status\ G

* * 1. Row *

Slave_IO_State:

Master_Host: 172.16.22.8

Master_User: mysql_sync

Master_Port: 3306

Connect_Retry: 5

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 426

Relay_Log_File: relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 426

Relay_Log_Space: 248

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

1 row in set (0.00 sec)

# by looking at the status of the slave server, we know that the IO thread and SQL thread of the slave server are not turned on, so let's start the replication process of the slave server node to achieve master-slave replication

MariaDB [(none)] > start slave

Query OK, 0 rows affected (0.00 sec)

# check the slave server status again, mainly focusing on the enabled status of IO threads and SQL threads:

MariaDB [(none)] > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.22.8

Master_User: repluser

Master_Port: 3306

Connect_Retry: 5

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 426

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 535

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: whether Yes # IOthread is running. If No means that slave is not working properly.

Slave_SQL_Running: whether Yes # SQLthread is running. If No means that slave is not working properly.

…… The intermediate information is slightly.

Seconds_Behind_Master: 0

Both slave's Icano and SQL threads are running, and Seconds_Behind_Master is no longer a NULL. The location of the log has increased, which means that some events have been obtained and executed. If you make changes on master, you can see changes in the location of various log files on slave, as well as changes in data in the database. The test is carried out below. Let's first look at the thread status of the master-slave node.

7. View the status of threads on master and slave. On master, you can see the connections created by slave's I / O thread:

Enter show processlist\ G on the Master node db01

MariaDB [(none)] > show processlist\ G

* * 1. Row *

Id: 5

User: mysql_sync

Host: 172.16.22.6:52875

Db: NULL

Command: Binlog Dump

Time: 477

State: Master has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

Progress: 0.000

* 2. Row * *

Id: 6

User: root

Host: localhost

Db: NULL

Command: Query

Time: 0

State: init

Info: show processlist

Progress: 0.000

2 rows in set (0.00 sec)

1.row is a connection that handles the I / O thread of the slave.

Run this statement on the Slave server node db02:

MariaDB [(none)] > show processlist\ G

* * 1. Row *

Id: 5

User: root

Host: localhost

Db: NULL

Command: Query

Time: 0

State: init

Info: show processlist

Progress: 0.000

* 2. Row * *

Id: 6

User: system user

Host:

Db: NULL

Command: Connect

Time: 587

State: Waiting for master to send event

Info: NULL

Progress: 0.000

* 3. Row * *

Id: 7

User: system user

Host:

Db: NULL

Command: Connect

Time: 587

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

Progress: 0.000

3 rows in set (0.00 sec)

The above 2.row is in the SQL thread state, and the 3.row is in the Imax O thread state.

8. Master-slave replication test

We create a database table on the master node db01 and enter into the test database

The database storage engine is innodb as far as possible.

After creating the table, let's go to the main node to check:

MariaDB [(none)] > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.03 sec)

MariaDB [(none)] > use test

Database changed

MariaDB [test] > show tables

+-+

| | Tables_in_test |

+-+

| | test |

+-+

1 rows in set (0.00 sec)

To check the replication status from the node, you can see that the replication has already taken place.

MariaDB [(none)] > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.22.8

Master_User: mysql_sync

Master_Port: 3306

Connect_Retry: 5

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 8640

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 8849

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 8640

Relay_Log_Space: 9140

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

1 row in set (0.00 sec)

By checking whether the database replication from the node has been completed, we can see that the test database table we imported exists:

MariaDB [(none)] > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.02 sec)

MariaDB [(none)] > use test

Database changed

MariaDB [test] > create table test

MariaDB [test] > show tables

+-+

| | Tables_in_hellodb |

+-+

| | test |

+-+

1 rows in set (0.01 sec)

Found that the test replication was successful

At this point, the master-slave replication of the database is completed.

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.