MARIADB Data replication Function implementation

Mariadb data replication Feature implementation

Lab Environment: Two servers IP : 192.168.1.117 ; IP : 192.168.1.118.

Experiment Objective: To realize the scale-out of database.

protocol: Server 192.168.1.117 as the primary node, the server 192.168.1.118. as slave node.

Experimental steps: divided into five steps to complete the experiment, first database installation, followed by the master-slave node configuration, re-copy the function to achieve, and finally do the test.

First, the installation of the database

Install here mariadb-5.5.42-linux-x86_64 version.

[email protected] ~]# mkdir-pv/mydata/data// Create the directory and do the data storage location. The hard drive should be hung in the actual application.

mkdir:created directory '/mydata '

mkdir:created directory '/mydata/data '

[[email protected] ~]# useradd mysql// Create MySQL user.

[Email protected] ~]# chown-r mysql.mysql/mydata/data

[[email protected] mariadb]# ls

Mariadb-5.5.42-linux-x86_64.tar.gz

[Email protected] mariadb]# tar XF mariadb-5.5.42-linux-x86_64.tar.gz-c/usr/local/

[Email protected] ~]# cd/usr/local

[[email protected] local]# ln-sv mariadb-5.5.42-linux-x86_64 mysql// after extracting, create the linked file.

' MySQL ', ' mariadb-5.5.42-linux-x86_64 '

[[Email protected] local]# CD MySQL

[Email protected] mysql]# chown-r root.mysql./*

[email protected] mysql]# mkdir/etc/mysql// here to create the directory drop MySQL 's configuration file.

[email protected] mysql]# CP support-files/my-large.cnf/etc/mysql/my.cnf//mysql 's configuration file.

[Email protected] mysql]# VIM/ETC/MYSQL/MY.CNF

Datadir=/mydata/data

[email protected] mysql]# CP support-files/mysql.server/etc/rc.d/init.d/mysqld

Cp:overwrite '/etc/rc.d/init.d/mysqld '? Yes

[Email protected] mysql]# chmod +x/etc/rc.d/init.d/mysqld

[email protected] mysql]# scripts/mysql_install_db--datadir=/mydata/data--user=mysql// the initialization of the database.

[[email protected] mysql]# Mkdir-pv/mydata/{binlogs,relaylogs}// binary log and trunk log storage location.

mkdir:created directory '/mydata/binlogs '

mkdir:created directory '/mydata/relaylogs '

[Email protected] mysql]#

[email protected] mysql]# chown-r mysql.mysql/mydata/// to be /mydata/ all files under the directory are defined as MySQL User MySQL Group.

[email protected] mysql]# vim/etc/profile.d/mysql.sh// Configuration MySQL of the PATH path.

Export Path=/usr/local/mysql/bin: $PATH

[email protected] mysql]# source/etc/profile.d/mysql.sh// to Here mariadb installation is basically complete.

Second, the master-slave node configuration of the database

[email protected] mysql]# vim/etc/mysql/my.cnf// define the master node here and switch to

Log-bin=/mydata/binlogs/mysql-master-bin

Binlog_format=mixed

Server-id = 1

[email protected] mysql]# vim/etc/mysql/my.cnf// This is defined from the node.

Relay-log=/mydata/relaylogs/mysql-relay-log

# Log-bin=mysql-bin

# binlog_format=mixed

Server-id = 11

[[Email protected] mysql]# service mysqld start

Starting MySQL ..... [OK]

[[email protected] mysql]# MySQL// after it is started, you can

Welcome to the MariaDB Monitor. Commands End With; or \g.

Your MariaDB Connection ID is 2

Server VERSION:5.5.42-MARIADB MariaDB Server

Copyright (c), Oracle, MariaDB Corporation Ab and others.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

MariaDB [(None)]>

Third, the database replication function implementation

On the master node, create an account with copy permissions.

MariaDB [(None)]> GRANT REPLICATION slave,replication CLIENT on * * to [e-mail protected] ' 192.168.1.118 ' identified by ' Repass ';

Query OK, 0 rows affected (0.06 sec)

MariaDB [(None)]> FLUSH privileges;

Query OK, 0 rows affected (0.12 sec)

MariaDB [(None)]> SHOW MASTER STATUS;

+-------------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+-------------------------+----------+--------------+------------------+

|      mysql-master-bin.000002 |              745 |                  | |

+-------------------------+----------+--------------+------------------+

1 row in Set (0.00 sec)

use the account link with copy permission on the slave node Master .

MariaDB [(None)]> change MASTER to master_host= ' 192.168.1.117 ', master_user= ' Reuser ', master_password= ' Repass ', Master_log_file= ' mysql-master-bin.000002 ', master_log_pos=745;

Query OK, 0 rows affected (0.22 sec)

MariaDB [(none)]> SHOW SLAVE status\g; Here's just a part.

1. Row ***************************

Slave_io_state:

master_host:192.168.1.117

Master_user:reuser

master_port:3306

Connect_retry:60

master_log_file:mysql-master-bin.000002

read_master_log_pos:745

relay_log_file:mysql-relay-log.000001

Relay_log_pos:4

relay_master_log_file:mysql-master-bin.000002

Slave_io_running:no

Slave_sql_running:no//io Threads and SQL The threads are not started.

in the From node data io Threads and SQL the thread is turned on.

MariaDB [(None)]> START SLAVE;

Query OK, 0 rows affected (0.12 sec)

MariaDB [(none)]> SHOW SLAVE status\g; View again is turned on.

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.1.117

Master_user:reuser

master_port:3306

Connect_retry:60

master_log_file:mysql-master-bin.000002

read_master_log_pos:832

relay_log_file:mysql-relay-log.000002

relay_log_pos:623

relay_master_log_file:mysql-master-bin.000002

Slave_io_running:yes

Slave_sql_running:yes

Four, do the next test, now create a database on the master node, and then view the database from the node

MariaDB [(None)]> CREATE DATABASE dbtest;

Query OK, 1 row affected (0.05 sec)

MariaDB [(None)]> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| dbtest |

| MySQL |

| Performance_schema |

| Test |

+--------------------+

5 rows in Set (0.06 sec)

MariaDB [(None)]>

MariaDB [(none)]> SHOW DATABASES; Viewing the database information from the node, it is clear that the experiment was successful.

+--------------------+

| Database |

+--------------------+

| Information_schema |

| dbtest |

| MySQL |

| Performance_schema |

| Test |

+--------------------+

5 rows in Set (0.30 sec)

MariaDB [(None)]>

MARIADB Data replication Function implementation