Record the process of mariadb setting master-Slave synchronization [virtual machine test]

Background: Because of the work of the relationship, need to find real-time synchronization of data to another system server database, query down, with MARIADB for cross-server synchronization data movement, with master-slave synchronization more, but also more insurance

There are also shell scripts that use shell scripts, timed to execute mysqldump, but if the crontab is broken, canceled, even time is changed, the database is altered, and so on, you need to do more.

But the shell script is still on the table and can be considered deeply.

Below, start the master-slave synchronization setup experience.

Front-facing preparation:

The first thing to do is to synchronize the tabular data that needs to be synchronized with mysqldump, and then the next master-slave synchronization ensures that the new users can run to another server in real time.

Synchronization command:

(master) ==> Export data:

Mysqldump-uroot-ppassword database table > 1.sql

　　

Import data (from) ==>:

Mysql-uroot-ppassword  Database < 1.sql

　　

First, Machine introduction

Master: 192.168,60,130

From: 192.168.60.129

OS Version: CentOS 7.4

MARIADB Version: 5.5

Second, configure the master server

1) Modify the MARIADB configuration file of the master server

Open mariadb configuration file/etc/my.cnf, and add the following under the [Mysqld] node:

#表示主服务器server-id = # record log log-bin=mysql-bin01  #提供数据同步服务的数据库, the data under this database can be synchronized to the slave server  binlog-do-db=# What database you need to back up here. Name of the database # set the database that does not log logs on the primary server  binlog-ignore-db=mysql   #日志过期时间  expire_logs_days=10  

　　

Note: If the configuration file already exists for the above items, overwrite the original

There is a need to restart the primary server's database ==> systemctl restart MARIADB, otherwise the configuration file modification does not take effect, cannot continue the following operation

2) View the status of the primary server

To open the database command line for this step, enter the command:

MariaDB [mysql]> Show Master status;+--------------------+----------+--------------+------------------+| File               | Position | binlog_do_db | binlog_ignore_db |+--------------------+----------+--------------+------------------+| mysql-bin01.000004 |      1243 | Tcloud       | MySQL            |+--------------------+----------+--------------+------------------+

This section needs to remember the values of file and position, and then the configuration needs to be used

3) View the status of the Log_bin

In the database command line, enter the command:

MariaDB [mysql]> Show variables like "%log_bin%"; +---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| Log_bin                         | On    | | log_bin_trust_function_creators | OFF   | | sql_log_bin                     | On    |+---------------------------------+-------+

　　

This section, if Log_bin is on, indicates normal open.

4) Create a master server user and permissions (specifically for creating a user from a server connection, and assigning a permission)

MariaDB [MySQL] > Grant replication Slave on * * to ' mysql ' @ ' 192.168.60.129 ' identified by ' 123456 '; #解释: Here is the creation of a MySQL User, the password is 123456, the database address connected to the computer is from the server's IP, that is, only allow the users to have access to the database from the server's IP and back up the table permissions

　　

It is also important to note that replication belongs to grant permissions, which can only be used with *. *, cannot specify a database or a data table, and can be specific to a data table if you specify normal permissions like select.

5) Restart the mariadb of the primary server

Third, configure the slave server

1) Modify the configuration file

Open the configuration file/etc/my.cnf and add the following under the [Mysqld] node:

#表示从服务器  server-id=2  #记录日志  log-bin=mysql-bin02  #同步的数据库   replicate-do-db= #要同步的数据库 # synchronized database specified table   replicate-do-db= #要同步的表  #不记录日志的数据库  replicate-ignore-db=mysql  #日志过期时间  expire_logs_days=10  

　　

2) Open the command line and execute the command to set the master information

Change Master to  master_host= ' 192.168.60.130 ', #主服务器地址  master_user= ' MySQL ', #主服务器数据库用户名  master_ Password= ' 123456 ', #主服务器数据库密码  master_log_file= ' mysql-bin01.000004 ',  #填写步骤2 (2) The value of the file entry  Master_log _pos=1243; Value of position in #填写步骤2 (2)  

　　

3) Restart from server mariadb

4) execute Start from server command (on from server database command line)

MariaDB [MySQL] > start slave;

　　

5) View slave's run status command

MariaDB [MySQL] > start slave;

　　

View the item with the user value of system user if its state value is "waiting for master to send event" and "Slave have read all relay log:waiting for the Slave i/ O thread to update it, the primary server is connected from the server.

6) View slave running status

MariaDB [MySQL] > show slave status;  

　　

If the value of slave_io_running is yes,slave_sql_running, the value is yes, and the server is running normally. If not, view the error log, which is typically located in the data directory.

Iv. Testing

Create a new database from the server database pension, create a new table in the primary server and insert the data, and view the database tables and data from the server databases.

Ps:

If the above modification is not normal, and the configuration file configuration in the middle of the problem, you need to first stop slave on the mariadb command line from the server, and then modify the other issues

After the modification is complete, check to see if the status of the primary server has changed show master status (focus on file and position parameters)

If the change is to be re-written, then on the slave server again to perform changes master to ... ==> start slave (such a process)

Record the process of mariadb setting master-Slave synchronization [virtual machine test]