Master-Slave synchronization, read/write separation, MySQL performance tuning (soft optimization)

configuring MySQL master-Slave synchronization
1 The role of Master-slave synchronization: Let the slave identity of the database server automatically synchronized

Master identity data on the database server.

First, the configuration of the primary database server 192.168.4.12
1 User Authorization
mysql> grant replication Slave on *.

[Email protected] "192.168.4.11" identified by "123456";
2 Enable Binlog logging
Vim/etc/my.cnf
[Mysqld]
Server_id=12
Log_bin=master12
binlog_format= "Mixed"
: Wq
#systemctl Stop Mysqld
#systemctl Start mysqld
#ls/var/lib/mysql/master12.*
#mysql-uroot-p123456
Mysql> Show master status;

Second, from the database server configuration 192.168.4.11
1 Verifying the authorized user of the main library
#ping 192.168.4.12
#mysql-h192.168.4.12-uslaveuser-p123456
Mysql> Show grants;

2 Modify configuration file to specify server_id
Vim/etc/my.cnf
[Mysqld]
server_id=11
: Wq

#systemctl Stop Mysqld
#systemctl Start mysqld

3 Set yourself to be that host from the library
#mysql-uroot-p123456
Mysql> Show slave status;
Mysql> Change Master to

Master_host= "192.168.4.12",
Master_user= "Slaveuser",
Master_password= "123456",
Master_log_file= "master12.000001",
master_log_pos=154;
Mysql>show slave status\g;
mysql> start slave;
Mysql>show slave status\g;
Slave_io_running:yes
Slave_sql_running:yes

Three-Test master-Slave synchronization configuration
The data that is generated when the client accesses the main library can be seen from the library.

Temporarily do not synchronize the data of the main library from the library
mysql> stop Slave;

Restore from library to stand-alone database server
#rm-rf master.info localhost-relay-bin.* relay-

Log.info
#systemctl Stop mysqld; Systemctl Start mysqld

Four, master-slave synchronous work process
cd/var/lib/mysql/
Master.info Connecting the main library information
LOCALHOST-RELAY-BIN.000001 Relay log files
Localhost-relay-bin.index Log Index file
Relay-log.info Relay Log Information

IO Thread: Responsible for saving the SQL commands in the main library binlog to the local relay day

Log file.

Error reason: Not connected to the main library server (ping SELinux firewalld

Grant Binlog log specified error)

Last_io_error: Error message

Resolve Errors
mysql> stop Slave;
mysql> change Master to option = value, option = value;
mysql> start slave;

SQL thread: Executes the SQL command in the native relay log file to write the data into the library

。
Error Reason: The library or table used when the SQL command is not executed in the log file

。

Last_sql_error: Error message

WORKAROUND: Let the master cubby own more data from the library, then stop slave; start slave;
++++++++++++++++++++++++++++++++++
V. Master-SLAVE synchronization structure
One Master one from *
A master many from *
Master slave from
Main master structure (mutual master and Slave)
++++++++++++++++++++++++++++++++++++
Common parameters for master-Slave synchronization configuration
Vim/etc/my.cnf
/etc/my.cnf
[Mysqld]
....
: Wq
#systemctl Restart Mysqld
#mysql >show Master status;

Parameters used by the master data server
BINLOG_DO_DB=DB1,DB2 only allow synchronization of native libraries 1 and 2
BINLOG_IGNORE_DB=DB1,DB2 only do not allow synchronization of native libraries 1 and 2

Parameters used from the data server
Log_slave_updates Allow cascade replication (master slave)
REPLICATE_DO_DB=DB1,DB2 only synchronize libraries 1 and 2
REPLICATE_IGNORE_DB=DB1,DB2 only the library 1 and Library 2 are not synchronized
relay_log= name Specifies the trunk log file name

V. MySQL read/write separation (third-party software + master-slave synchronization)
1 What read and write separation: Handle different operations to different database servers
2 Why Read and write separation: Reduce the concurrent access pressure on the server while increasing the hard

Utilization of the components.

Implement read-Write separation
Third-party software: There are a lot of MySQL middleware different middleware software has not

Most of the same features are open source (Mysql-proxy mysql-mmm

Mycat maxscale*)

Master-Slave synchronous structure (one-from-one)
Requirement: Configure database server 11 as the slave server for database server 12.

1. Configuration 12
1.1 User Authorization
1.2 Enable Binlog Logging

2. Configuration 11
2.1 Testing Authorized users
2.2 Setting server_id
2.3 Administrator account login, set yourself to 12 from the database server
2.4 Viewing the status of IO threads and SQL threads

3. Test the master-slave synchronization configuration on the client
3.1 Adding users who have access to the data on the primary database server
Mysql> Grant all on bbsdb.* to [email protected] "%"

Identified by "123456";
mysql> CREATE DATABASE Bbsdb;

Using an authorized user on the primary database server to connect to the primary database server, build the library, build

Table, when you connect from the database, you can also see the newly created libraries and tables.
#mysql-h192.168.4.12-uwebadmin-p123456

3 Configuring a server that implements read-write separation (13)
3.1 Ability to communicate with 2 database servers
#ping 192.168.4.11/12
3.2 Installing the Software maxscale-2.1.2-1.rhel.7.x86_64.rpm
#rpm-IVH maxscale-2.1.2-1.rhel.7.x86_64.rpm (as prompted

Install dependent packages)
#rpm-QL Maxscale
#rpm-QC Maxscale

3.3 Modifying a configuration file
# grep-v ' # '/etc/maxscale.cnf.template >

/etc/maxscale.cnf

[Email protected] ~]# CAT/ETC/MAXSCALE.CNF
[Maxscale]
Threads=1

[Server1]
Type=server
address=192.168.4.11
port=3306
Protocol=mysqlbackend

[Server2]
Type=server
address=192.168.4.12
port=3306
Protocol=mysqlbackend

[MySQL Monitor]
Type=monitor
Module=mysqlmon
Servers=server1,server2
User=scalemon
passwd=111111
monitor_interval=10000

[Read-write Service]
Type=service
Router=readwritesplit
Servers=server1,server2
User=maxscale
passwd=111111
max_slave_connections=100%

[Maxadmin Service]
Type=service
Router=cli

[Read-write Listener]
Type=listener
Service=read-write Service
Protocol=mysqlclient
port=4006

[Maxadmin Listener]
Type=listener
Service=maxadmin Service
Protocol=maxscaled
Socket=default
port=4008
[Email protected] ~]#

[Maxscale] Defines the number of threads that are started by the service runtime
[Server number] defines the database server IP address
[MySQL Monitor] defines a list of monitoring members
[Read-write Service] defines the read-write detach host list
[Maxadmin service] defines management services
[Read-write Listener] defines the ports used by the read-write separation service
[Maxadmin Listener] defines the ports used by the management service

Add the appropriate authorized user on the database server based on the settings in the configuration file
Mysql>grant replication Slave, replication Client on *. *

to [e-mail protected] '% ' identified by "111111";

Mysql> Grant Select on mysql.* to [email protected] '% '

Identified by "111111";

3.4 Starting the Service
# maxscale-h
# maxscale-f/ETC/MAXSCALE.CNF
[Email protected] ~]# Netstat-utnalp | grep Maxscale
TCP 0 0 192.168.4.13:39870 192.168.4.12:3306

Established 12412/maxscale
TCP 0 0 192.168.4.13:32980 192.168.4.11:3306

Established 12412/maxscale
TCP6 0 0::: 4008:::* LISTEN

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

12412/maxscale
[Email protected] ~]#

Viewing the status of a monitoring host on a native connection management port
[Email protected] ~]# maxadmin-p 4008-pmariadb
maxscale> list Servers
Servers.
-------------------+-----------------+-------+-------------

+--------------------
Server | Address | Port | Connections |

Status
-------------------+-----------------+-------+-------------

+--------------------
Server1 | 192.168.4.11 | 3306 | 0 | Slave,

Running
Server2 | 192.168.4.12 | 3306 | 0 | Master,

Running
-------------------+-----------------+-------+-------------

+--------------------
Maxscale> list Ser
Unknown or missing option for the list command. Valid

Sub-commands is:
Clients List all clients
Dcbs List All Dcbs
Filters List All Filters
Listeners List all listeners
Modules List all currently loaded modules
Monitors List all monitors
Services List all the services
Servers List all servers
Sessions List all sessions
Threads List Polling Threads
Commands List Registered commands
Maxscale> quit

4 test read/write separation on client
Mysql-h192.168.4.13-p 4006-uwebadmin-

p123456
Mysql> select * from BBSDB.A;
Insert insert into BBSDB.A values (1001);

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
MySQL performance tuning (soft optimization)
What could be the cause of the maintenance database server responding slowly to client requests?
1.1 Narrow network bandwidth (using Network velocimetry Software)

1.2 Server hardware configuration too low (CPU memory storage)
Monitor usage with command or monitoring software (top free-m)

1.3 Low database service software version available
How to know if the software version is low, view parameter information when the service is running
/etc/my.cnf
[Mysqld]
Variable name = value
: Wq

#mysql-uroot-p123456
View parameter values
Mysql> Show variables like "%timeout%";
Mysql> Show variables like "Connect_timeout";

modifying parameter values
mysql> Set Variable name = value;
Mysql> SET global variable name = value;

mysql> SET GLOBAL connect_timeout=5;

Settings for common variables:
1 Number of concurrent connections max_connections
Mysql> Show Processlist

Mysql> Show global status Like "Max_used_connections";

max_used_connections/max_connections=0.85

2 Setting up a connection 3 times the handshake timeout period
Connect_timeout

3 When the connection is established, close the timeout period for an inactive connection
Wait_timeout 28800

4 Threads: Number of threads allowed to be reused in the cache
Thread_cache_size

5 Number of open tables cached for all threads
Table_open_cache

6 parameters related to query cache

SELECT * from T1; Read_buffer_size

SELECT * from t1 where name is like "a%"; Key_buffer-size
SELETCT * from T1 GROUP by sex; Read_rnd_buffer_siz
SELETCT * from T1 order by score; sort_buffer_size

Query optimization
Describe the process by which a MySQL data server processes query requests?

Mysql> Show variables like "query_cache%";
Query_cache_type=0/1/2

0 No Deposit Allowed
1 Unconditional Storage
2 to set the query results to be recorded in the query cache before storage

Select In_sql_cache * from T1;

Query_cache_wlock_invalidate off
Tables for the MyISAM storage engine

PC1 select name from T1 where name= "Jim"
Name= "Jim"

PC2 select name from T1 where name= "Jim";
PC3 Update T1 set name= "Tom" where Name= "Jim";

Display Query Cache statistics
Mysql> Show global status Like "qcache%";
Qcache_hits 30
Qcache_inserts 100
Qcache_lowmem_prunes 10
Qcache_not_cached

1.4 Programmer's SQL command to access data is complex, resulting in slow processing
The MySQL database service enables slow query logging, which logs after the client has connected the service,

Displays the query result SQL command over the specified time.

What is the log type of the MySQL database service?
Error log binlog log query log slow query log
Enabled by default

Vim/etc/my.cnf
[Mysqld]
Options
: Wq
#systemctl Stop mysqld;
#systemctl start mysqld;

Error log: Log error messages generated by the service during startup and operation
Log-error=/var/log/mysqld.log

Query log: Records all SQL commands that are executed after the client connects to the service.
[Mysqld]
General-log

cat/var/lib/mysql/host name. Log

Slow query log: Displays the query result SQL command after the Client connection service is logged for more than a specified time.

[Mysqld]
Slow-query-log

Mysql> Select Sleep (11);
# cat/var/lib/mysql/Host name-slow.log
#mysqldumpslow/var/lib/mysql/host name-slow.log >/tmp/sql.txt

1.5 Unreasonable network structure

Master-Slave synchronization, read/write separation, MySQL performance tuning (soft optimization)