6\mysql master-Slave synchronization, MySQL read/write separation, MySQL performance tuning

Day06
First, MySQL master-slave synchronization
Second, the data read and write separation
Third, MySQL optimization
++++++++++++++++++++++++++++++++
First, MySQL master-slave synchronization
1.1 Introduction to Master-slave synchronization? Automatically synchronize data on the main library from the library server (the database server accessed by the client is the Master library server)
1.2 Structure

              54                 55

Systemctl start mysqld systemctl start mysqld
Master master database server from slave database server

1.3 Configuring the master-slave synchronization structure?
1.3.1 Configuration of the main library role host
1 User Authorization
mysql> grant replication Slave on . to [email protected] ' 192.168.4.55 ' identified by ' 123456 ';

2 Enable Binlog logging
Ten VIM/ETC/MY.CNF
server_id=54
Log-bin=master54
binlog-format= "Mixed"
: Wq
3 Restarting the database server
Systemctl Stop Mysqld
Systemctl Start mysqld
ls/var/lib/mysql/master54.*

4 View the Binlog log file being used.

mysql-uroot-p123456

Mysql> Show master status;

1.3.2 from the configuration of the library role host?
1 show if you are from the database server
Mysql> Show slave status;

2 testing whether the user authorized by the Master library is valid
#mysql-h192.168.4.54-uyaya-p123456
Mysql> Show grants;
Mysql> quit

3 Modify configuration file to specify server_id
#vim/etc/my.cnf
[Mysqld]
Server_id=55
: Wq
#systemctl Restart Mysqld

4 Specify the main library information in the native Data administrator login.
Mysql> Change Master to
-master_host= "192.168.4.54",
-Master_user= "Yaya",
-master_password= "123456",
-master_log_file= "master54.000001",
master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.35 sec)
mysql> start slave;
Mysql> show Slave status\g;
Slave_io_running:yes
Slave_sql_running:yes

Verifying master-Slave synchronization configuration: Results
The client connects to the new data generated by the main library server, which is visible from the host on the role.

+++++++++++++++++++++++++++++++
How does the master-slave work?
IO thread: Logs the Binlog log contents of the master library to the Relay-binlog log in this machine.

SQL thread: Executes the SQL command in the native Relay-binlog log to write the data into the native library.

The following files are available from the database directory of the role host:
Master.info storing information about the primary database
Slave55-relay-bin. Xxxxxx
Relay log files The SQL command that the machine generates new data
Slave55-relay-bin.index Relay log file Index file
Relay-log.info records the trunk log files used by the current database server

Master-Slave synchronization configuration troubleshooting?
IO thread Error: The information from the library to specify the main library is incorrect;
Security restrictions (firewall SELinux)

To view the error message:
Last_io_error: Error message

mysql> stop Slave;
mysql> change mstart to option = value;
mysql> start slave;

SQL thread Error: When executing the SQL command in the relay log, the command Operation library or table does not exist on this machine.

To view the error message:
Last_sql_error: Error message
mysql> stop Slave;
Libraries or tables that have command operations from this machine
mysql> start slave;

++++++++++++++++++++++++++++
Let the data from the main library be temporarily out of sync with the database?
mysql> stop Slave;

Restore from library to stand-alone database server?
#rm-rf master.info slave55-relay-bin.* relay-log.info
#systemctl Restart Mysqld

Mysql> Show slave status;

Master-Slave synchronous structure mode?
One Master one from
A master more from
Master slave from
Mutual master from (main master structure)

Reset Master #清空主的日志
Reset slave #只清空日志
Reset slave all #需要重新配置change Master

++++++++++++++++++++++++++++++++++++
Master-Slave synchronization common configuration parameters? (/ETC/MY.CNF)
Configuration parameters for the main library role host
[Mysqld]
binlog_do_db= database name, database name #只允许同步的库binlog_ignore_db = database name, database name #只不允许同步的库

Mysql> Show Master stauts;

Configuration parameters from the library role host
[Mysqld]
Log_slave_updates Cascade Replication
replicate_do_db= database name, database name #只同步的库
replicate_ignore_db= database name, database name #只不同步的库relay_log = file name #设置中继日志文件名称
+++++++++++++++++++++++++++++
Second, the data read and write separation
2.1 Introduction to read/write separation? The operation of the client's query record and the operation of the write data are performed on different database servers.

2.2 Why do read and write separations? Reduce concurrent access pressure on a single database server and provide utilization of server hardware resources

2.3 Configuration data read/write separation (Maxscale + master-Slave synchronization structure)

2.3.1 Topological structure
Client
|
Maxscale 4010 Management Port 4006 read/write Detach port

Insert Select
Write a check
54 55
Slave

2.3.2 configuration MySQL One master one from the synchronous structure
2.3.3 Configuration Maxscale (53)
A. Packaging
B. Modifying a configuration file
Vim/etc/maxscale.cnf
9 [Maxscale]
Ten Threads=1

[Server1]
Type=server
address=192.168.4.54
port=3306
Protocol=mysqlbackend
23

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

[MySQL Monitor]
Type=monitor
Module=mysqlmon
Servers=server1, Server2
User=scalemon # Monitoring the running state and master-slave status of the database service service
passwd=123456
monitor_interval=10000

[Read-write Service]
Type=service
Router=readwritesplit
Servers=server1, Server2
User=maxscale #检查接收客户端连接请求时, whether the connected user name and password exist on the database server
passwd=123456
max_slave_connections=100%

[Maxadmin Service]
Type=service
Router=cli

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

[Maxadmin Listener]
101 Type=listener
102 Service=maxadmin Service
103 protocol=maxscaled
104 Socket=default
port=4010

Add the corresponding authorized user on the database server based on the settings of the configuration file.
mysql> grant replication Slave, replication Client on . to [email protected] '% ' identified by

"123456";

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

C. Start the service

Systemctl Stop Mysqld

[Email protected] ~]# maxscale-f/etc/maxscale.cnf
[Email protected] ~]# Netstat-utnalp | grep:4010
TCP6 0 0::: 4010::: LISTEN 9559/maxscale
[Email protected] ~]#
[Email protected] ~]# Netstat-utnalp | grep:4006
TCP6 0 0::: 4006:: : LISTEN 9559/maxscale
[Email protected] ~]# Netstat-utnalp | grep maxscle
[Email protected] ~]# Netstat-utnalp | grep Maxscale
TCP 0 0 192.168.4.53:52855 192.168.4.55:3306 established 9559/maxscale
TCP 0 0 192.168.4.53:52228 192.168.4.54:3306 established 9559/maxscale
TCP6 0 0::: 4010::: LISTEN 9559/maxscale
TCP6 0 0::: 4006:: : LISTEN 9559/maxscale
[Email protected] ~]#
53 Accessing the Local Management Service view the status of the monitoring host
[Email protected] ~]# maxadmin-p4010-uadmin-pmariadb
Maxscale>list servers

Stop Maxscale Service
#ps-C Maxscale
#kill-9 PID number

D. Testing the configuration on the client
#ping-C 2 192.168.4.53

D.1 adding users who access data to the database server
Mysql> Grant all on . to [email protected] '% ' identified by "123456";

D.2 Access Proxy Host
#mysql-p4006-h192.168.4.53-ustudent-p123456

+++++++++++++++++++++++++++++++++++++
Third, MySQL optimization (optimization ideas, optimization of what, how to optimize)
When accessing data, the results are particularly slow. The analysis may be caused by what causes.

1 Low hardware configuration: View application device usage CPU memory storage (hard disk)
Top 0.0 WA
Sar
Uptime I/O

                    free  -m

2 Network bandwidth: Using network speed software

3 The database service software version is provided Low:
To view the run parameter configuration for the database service runtime (common configuration parameters)

Timeout period
Connect_timeout
Time-out for three handshake when establishing a connection
Wait_timeout waiting for a disconnect after establishing a connection timeout

Mysql> Show variables like "Connect_timeout";
Mysql> Show variables like "Wait_timeout";

Number of threads allowed to reuse
Mysql> Show variables like "thread_cache_size";

The number of open tables that are cached for all threads.
table_open_cache=2000
Mysql> Show variables like "Table_open_cache";
PC1 T1

PC2 T3 mysqld

PC3 T1

Key_buffer-size
Sort_buffer_size
Read_buffer_sizeread_rnd_buffer_sizename Index
Select name from user where name= "Zhangsan";
Select Name,uid from the user order by uid Desc; <----mysqld
select * from user; <------mysqld
Select shell from user group by shell;<------MYSQLD

Query cache related parameter settings?
Mysql> Show variables like "query_cache%";

Query cache write lock efficient storage engine (MYISAM)
Query_cache_wlock_invalidate | OFF

       t1

PC1 select name from user where name= "Lucy";
Name= "Lucy"------> Query Cache

PC2 select name from user where name= "Lucy";--> query cache
Name= "Lucy"

PC3 Update user Set name= "Lili" where Name= "Lucy";

Query_cache_type 0|1|2
0 off do not store
1 Open Unconditional Storage
2 Open the specified storage before storing

Query_cache_limit 1048576 More than 1M this number does not exist
Query_cache_min_res_unit 4096 minimum storage unit 4k

Query Query cache statistics?
Mysql> Show global status Like "qcache%";
Qcache_hits 0
Qcache_inserts 0
Qcache_lowmem_prunes 0

Number of concurrent connections:
Mysql> Show variables like "max_connections";

The maximum number of connections that have ever been made
Mysql> Show global status Like "Max_used_connections";

Formula:
Maximum number of connections/concurrent connections = 0.85 * 100% = 85%
3000/x = 0.85

View the value of a parameter
Mysql> show variables like "% keyword%";
Mysql> Show variables like "%password%";
Mysql> Show variables like "Old_passwords";

Temporarily define mysql> set [global] Variable name = value;

Permanently defined
Vim/etc/my.cnf
[Mysqld]
Variable name = value
....
: Wq
#systemctl Restart Mysqld

MySQL architecture?
Connection pool
SQL interface
Analyzer
Optimizer
Query cache
Storage Engine
File system
Management tools

How does the MySQL service process query requests?

4 The SQL query command of the query database written by the program is unreasonable, resulting in slow database processing
Enable slow query log on the database server: Records SQL commands that display query results more than the specified time. (tolerable value 3 seconds)

5 Data storage architecture settings have data transmission bottlenecks.

6\mysql master-Slave synchronization, MySQL read/write separation, MySQL performance tuning