DBA growth Path---mysql master-slave synchronization, read/write separation

master-Slave synchronization

What is master-slave synchronization: Let other database servers automatically synchronize data on the database server that is serving the service.

1 Adding authorized users

mysql> grant replication Slave on * * to [e-mail protected] "192.168.4.4" identified by ' 123456 ';

2 Enable Binlog logging

[Email protected] ~]# VIM/ETC/MY.CNF

[Mysqld]

Server_id=3

Log-bin=master11

binlog_format= "Mixed"

Restarting the database service

[Email protected] ~]# systemctl restart mysqld

4 Viewing log information

Mysql> Show master status;

master11.000001

Verifying the authorized user provided by the main library

[Email protected] ~]# mysql-h 192.168.4.3-u slaveuser-p123456

Modifying a configuration file

[Email protected] ~]# VIM/ETC/MY.CNF

[Mysqld]

Server_id=4

Log-bin=db4

[Email protected] ~]# systemctl restart mysqld

Log in using the Data administrator of this machine to specify the main library information

show slave status;

mysql> Change Master to master_host= "192.168.4.3", master_user= "Slaveuser", master_password= "123456", Master_log_ File= "master11.000001", master_log_pos=154;

Mysql> show Slave status\g;

master_log_file:master11.000001

read_master_log_pos:154

Slave_io_running:no

Slave_sql_running:no

mysql> start slave;

Mysql> show Slave status\g;

Slave_io_running:yes

Slave_sql_running:yes

Slave Running 2 threads

slave_io: Copy the SQL from the Master host Binlog log file into the native Relay-log file

Slave_sql: Executes the SQL statement in the native Relay-log file to reproduce the master's data operations

More files from the database directory

Master.info connecting master server information

Relay-log.info Relay log Information

Host name-relay-bin.000000 relay log file

Host name-relay-bin.index Trunk Log Index

Test master-Slave synchronization configuration

To add users who access data on the primary library server

Mysql> Grant all on bbsdb.* to [e-mail protected] "%" identified by ' 123456 ';

Mysql> Show master status;

|      master11.000001 | 446

When the client uses an authorized user to connect to the main library, the resulting data can be found from the library

[Email protected] ~]# mysql-h 192.168.4.3-uyaya-p123456

MySQL [(None)]> CREATE Database bbsdb;

At this time

[[email protected] ~]# Primary server

[[email protected] ~]# from the server

will generate the corresponding table on the

will be changed from server to standalone server, shutdown synchronization

mysql> stop Slave;

RM-RF Master.info Relay-log.info

RM-RF localhost-relay-bin*

Fault analysis and elimination

Cause analysis

Cannot connect to the master database server

Solutions

Check the physical connection ping Check authorized users

Check if there are firewall rules

Turn off SELinux

or Binlog log file specifies an error

Master-Slave synchronous structure mode

One Master one from

A master more from

Master slave from

Main master structure (inter-)

Master-Slave synchronization Common configuration parameters

Written in vim/etc/my.cnf

[Mysqld]

option = value

Main Library configuration file (valid for all from)

Libraries that are not allowed to synchronize binlog_ignore_db= library name 1, library name 2

Allow only synchronized libraries binlog_do_db= Library name 1, library name 2

[Email protected] ~]# VIM/ETC/MY.CNF

Binlog_do_db=dba

[Email protected] ~]# systemctl restart mysqld

from the Library configuration file (only valid for this machine)

Libraries that are synchronized only replicate_do_db= library name 1, library name 2

Out of sync library replicate_ignore_db= Library name 1, library name 2

Cascading replication log_slave_updates

data read/write separation Maxscale Software + one master one from

What is read-write separation: Query requests for client access to data select and write requests insert to different database servers for processing

Write (Master service)

Read (from service)

One.

Loading package

[Email protected] ~]# RPM-IVH maxscale-2.1.2-1.rhel.7.x86_64.rpm

Modify configuration file: Specify the database server

[Email protected] ~]# VIM/ETC/MAXCALE.CNF

9 [Maxscale]

Threads=auto Open thread based on CPU

[Server1]#数据库服务器名

Type=server

address=192.168.4.3#指定ip

port=3306

Protocol=mysqlbackend

23

[Server2]#数据库服务器名

Type=server

address=192.168.4.4#指定ip

port=3306

Protocol=mysqlbackend

[MySQL Monitor]

Panax Notoginseng Type=monitor

Module=mysqlmon

Servers=server1,server2#监听两台 database server

User=scalemon#授权用户名

passwd=123456#授权用户密码

monitor_interval=10000#毫秒 Listen once every 10 seconds

#[read-only Service]#Read-only Full Comment

#type =service

=readconnroute #router

#servers =server1

#user =myuser

=mypwd #passwd

=slave #router_options

[Read-write Service]

Type=service

Router=readwritesplit

Servers=server1,server2#监听两台 database server

User=maxscale#授权用户名

passwd=123456#授权用户密码

max_slave_connections=100%

[Maxadmin Service]#定义软件管理服务 do not change

Type=service

ROUTER=CLI#使用方式 command line

#[read-only Listener]#Read-only Full Comment

#type =listener

#service =read-only Service

=mysqlclient #protocol

#port =4008

[Read-write Listener]

Type=listener

Service=read-write Service#服务名

94 Protocol=mysqlclient

port=4006#读写分离监听端口号 (default)

[Maxadmin Listener]#管理服务监听

98 Type=listener

Service=maxadmin Service

Protocol=maxscaled

101 Socket=default

102 port=4009#指定管理端口

Add the appropriate authorized user to the database based on the configuration file settings

Scalemon123456 User Monitoring database service status (database service status and master-slave synchronization State)

Mysql> Grant replication Slave,replication Client on * * to [e-mail protected] '% ' identified by ' 123456 ';

Mysql> Grant Select on mysql.* to [e-mail protected] '% ' identified by ' 123456 ';

Maxscale123456 checks whether the user name and password used by the client connection exist on the monitored database server

In the agent host test

mysql-h192.168.4.3-uscalemon-p123456

mysql-h192.168.4.3-umaxscale-p123456

mysql-h192.168.4.4-umaxscale-p123456

mysql-h192.168.4.4-uscalemon-p123456

Start the service

[Email protected] ~]# maxscale-f/etc/maxscale.cnf

[Email protected] ~]# Netstat-pantu | grep Maxscale

TCP 0 0 192.168.4.5:42703 192.168.4.3:3306 established 4877/maxscale

TCP 0 0 192.168.4.5:44918 192.168.4.4:3306 established 4877/maxscale

TCP6 0 0::: 4009:::* LISTEN 4877/maxscale

TCP6 0 0::: 4006:::* LISTEN 4877/maxscale

Two.

Test configuration

On the proxy server native Access management Service

[Email protected] ~]# maxadmin-uadmin-pmariadb-p4009

maxscale> list Servers

Servers.

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

Server | Address | Port | Connections | Status

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

Server1 |  192.168.4.3 |           3306 | 0 | Master, Running

Server2 |  192.168.4.4 |           3306 | 0 | Slave, Running

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

Store data or query data on the client Access Proxy

4-3

mysql> CREATE DATABASE Bbsdb;

Query OK, 1 row Affected (0.00 sec)

Mysql> CREATE TABLE bbsdb.t1 (id int);

Query OK, 0 rows affected (0.26 sec)

MySQL [(none)]> show grants;

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

| Grants for [email protected]% |

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

| GRANT USAGE on * * to ' yaya ' @ '% ' |

| GRANT all Privileges "bbsdb". * to ' yaya ' @ '% ' |

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

2 rows in Set (0.00 sec)

View when displayed as from service

MySQL [(None)]> SELECT @ @hostname;

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

| @ @hostname |

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

| 4-4mysql |

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

1 row in Set (0.00 sec)

MySQL [(None)]> insert into BBSDB.T1 values (100);

Switch to primary service after adding

MySQL [(None)]> SELECT @ @hostname;

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

| @ @hostname |

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

| 4-3mysql |

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

1 row in Set (0.00 sec)

DBA growth Path---mysql master-slave synchronization, read/write separation