mysql1.6 (Master-Slave synchronization, data read/write separation, mysql optimization)

Directory: one, master-slave synchronization

Second, data read and write separation

Third, MySQL optimization

One, MySQL master-slave synchronization.

1.1 What is master-slave synchronization

1.2 How master-Slave synchronization works

1.3 Configuring Master-Slave synchronization and verifying configuration

1.4 Master-Slave synchronous structure mode

1.5 Common configuration parameters for master-slave synchronization

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1.1 What is master-slave synchronization?

Keep the data on the other MySQL database server and the database server being accessed consistent.

1.2 How does master-slave sync work?

IO thread: Save the SQL command in the main library Binlog log to the local relay log file

SQL thread: Executes the SQL command in the native relay log file and writes the data into the local database server.

1.3 Configuring Master-Slave synchronization and verifying the configuration

Configuration steps for the main library:

(1) User authorization: Grant replication Slave on * * to [e-mail protected] "192.168.4.52" identified by "123456";

(2) Use Binlog log:

Vim/etc/my.cnf

[Mysqld]

server_id=51
Log-bin=/binlogdir/master51
binlog_format="Mixed"

Mkdir/binlogdir

Chown mysql/binlogdir/

Systemctl Restart Mysqld

(3) View Binlog file information in use

Mysql-> Show master status;

Environment readiness: Turn off the firewall, SELinux, and ensure that the data in the main library is consistent with the data from the library.

Keep the initial 4 libraries.

Configuration steps from the library

Test Main Library User authorization: mysql-h192.168.4.51-uplj-p123456

Specify server_id

VIM/ETC/MY.CNF s

[Mysqld]

server_id=52
Log_bin=/mylog/slave52

Database Management Configuration Master Library information

Mkdir/mylog

Chown-r Mysql/mylog

Systemctl restart mysqld; mysql-uroot-p123456

Initiate a synchronous operation:

mysql-> Change Master to master_host= ' master server IP ',

-master_user= ' PLJ ', (authorized user name)

-master_password= ' password ', (authorized user password)

master_log_file= ' log file ',//can show master status on the main

master_log_pos= offset position; You can show master status above the master

Start slave start service //If you want to change information later, stop slave is going to modify it.

Viewing native IO threads and SQL threads

MySQL--show slave status\g;

Mysql->stop slave;

Mysql->start slave;

Last_io_error:io Process error message

Last_sql_error:sql Process error message

Files that come in multiple places under the database directory under the library:

Master.info Record the main library information

Hostname-relay-bin. number Relay log file

Hostname-relay-bin.index Index file (records existing trunk log files)

Relay-log.info Logging relay Log information

Main library: Show Processlist;

You can see that there's a binlog dump. Each time the new SQL command notifies the I/O thread above the library to implement the synchronous update.

Achieve the final effect: from the library to achieve increase, delete, change the real-time synchronization. All operations on the main library

There will be data on the library.

Temporarily shut down from library: Stop slave; show slave status;

Cancel from library definition: Delete several files under the home directory

RM-RF Master.info Relay-log.info mysql52-relay-bin.*

re-start service ****************************************************************************************** 1.4 Structure mode of master-slave synchronization

One Master one from

A master more from

Master slave from

Main Master Structure

1.5 master-Slave synchronization Common configuration parameters (/ETC/MY.CNF)

Configuration parameters for the main library (the settings in the main library are valid for all)

binlog_do_db= Library Name list, interval symbol #只允许同步的库

binlog_ignore_db= Library Name list, interval symbol #只不允许同步的库

Configuration parameters from the library (only valid from the library itself)

Log_slave_updates//Allow cascade replication

relay_log= file name//Specify the name of the trunk log file

replicate_do_db= Library Name list, interval symbol #只同步的库

replicate_ignore_db= Library Name list, interval symbol #只不同步的库

*************************************************************************************

Second, data read/write separation (Maxscale software + one master one from the structure)

MySQL middleware (software that provides services together with MySQL services)

Software for read and write separation: Mycat mysql-proxy Maxscale

2.1 What is data read/write separation

Query requests and write requests when accessing data from a client

Processed separately for different database servers.

2.2 Why do read and write separations

Reduce concurrent access pressure on a single server while improving hardware utilization.

3.3 Configuring read-write separation

(1) Configure Master-Slave synchronization (one master one from 51 (master) 52 (from))

(2) Configuring Proxy Server 55

1. Stop the database service and set the boot to not run, disable SELinux and FIREWALLD

2, Package modify profile User Licensing Start service view port number

RPM-UVH maxscale-2.1.2-1.rhel.7.x86_64.rpm

cp/etc/maxscale.cnf/root/

Vim/etc/maxscale.cnf

9 [Maxscale]
Threads=auto //The thread used to automatically match the CPU

[Server1]: Define the listening host 1
Type=server
address=192.168.4.51
port=3306
Protocol=mysqlbackend
23
[Server2]: Define the listening host 2
Type=server
address=192.168.4.52
port=3306
Protocol=mysqlbackend

[MySQL Monitor]
Panax Notoginseng Type=monitor
Module=mysqlmon
servers=server1,server2//definition of monitoring host
User=scalemon//Monitor the host's login user
passwd=123456
monitor_interval=10000

#[read-only Service]//Comment Line
=service #type
#router =readconnroute
#servers =server1
=myuser #user
=mypwd #passwd
#router_options =slave

[Read-write Service]
Type=service
Router=readwritesplit
Servers=server1,server2 //Add additional hosts
User=maxscale //To detect the presence of the client login user
passwd=123456
max_slave_connections=100%

#[read-only Listener]//comment out these lines
#type =listener
#service =read-only Service
#protocol =mysqlclient
#port =4008

[Read-write Listener]
Type=listener
94 Service=read-write Service
Protocol=mysqlclient
port=4006
97
98 [Maxadmin Listener]
Type=listener
Service=maxadmin Service
101 protocol=maxscaled
102 Socket=default
103 port=4016//extra lines added

Authorization on the master/slave database:

Grant replication Slave,replication Client on * * to

-e [email protected] '% ' identified by "123456"; Authorization to Maxscale software connection user listening permission

Grant Select on mysql.* to [email protected] '% ' identified by "123456"; Authorization Maxscale software maxscale user query permission, to detect the presence of code login user.

Test:MYSQL-H51/52 host ip-u authorized user-p password to test whether you can log in

3, access the management port to view the master-slave synchronization status information and the status of the database service

Maxscale-f/etc/maxscale.cnf//start-up service

Netstat-antpul | grep Maxscale//view process

Ps-c Maxscale//view process PID

Stop service: killall-9 Maxscale

#maxadmin-uadmin-pmariadb-p4016

List servers//lookup read/write detach client running state.

3.4 Test configuration? In the client test configuration

Note: Tony manually goes to the master service to authorize a library. * Authorize all permissions.

#mysql-h192.168.4.55-p4006-utony-p123456

MySQL-I use a query statement/a write command.

Test whether read-write separation, read on 52 server, write on 51 server.

3.5 What are the drawbacks of configuring a detached structure?

Single point of failure, when the amount of data is large. Data transmission will have bottlenecks.

3.6 Data read/write separation topology

4.254

|

|

55 (Install Maxscale software)

+++++++++++++++++++++++++++

51 Master 52 from

mysql1.6 (Master-Slave synchronization, data read/write separation, mysql optimization)