Shulou(Shulou.com)06/01 Report--

Day06

1. Master-slave synchronization of mysql

2. Separation of data reading and writing

III. MySQL optimization

+ +

1. Master-slave synchronization of mysql

1.1 Master-slave synchronous introduction? Automatically synchronize the data on the master database from the library server (the database server accessed by the client serves as the master database server)

1.2 structure

54 55

Systemctl start mysqld systemctl start mysqld

Master master database server slave slave database server

1.3 configure master-slave synchronization structure?

1.3.1 configuration of the main library role host

1 user authorization

Mysql > grant replication slave on. To yaya@ "192.168.4.55" identified by "123456"

2 enable binlog Log

10 vim / etc/my.cnf

Server_id=54

Log-bin=master54

Binlog-format= "mixed"

: wq

3 restart the database server

11 systemctl stop mysqld

12 systemctl start mysqld

13 ls / var/lib/mysql/master54.*

4 View the binlog log file that is in use.

Mysql-uroot-p123456

Mysql > show master status

1.3.2 what is the configuration of the host in the role of the library?

1 shows whether you are from the database server

Mysql > show slave status

2 test whether the authorized user of the main library is valid

# mysql-h292.168.4.54-uyaya-p123456

Mysql > show grants

Mysql > quit

3 modify the configuration file to specify server_id

# vim / etc/my.cnf

[mysqld]

Server_id=55

: wq

# systemctl restart mysqld

4. Log in to the local data administrator to specify the main library information.

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

Verify master-slave synchronization configuration: result

The new data generated by the client connecting to the main library server can be seen on the host of the slave role.

+ +

Master-slave working principle?

IO thread: record the binlog log contents of the master library to the local relay-binlog log.

SQL thread: execute the sql command in the native relay-binlog log to write the data to the native library.

The following files will be added from the database directory of the role host:

Master.info stores the information of the master database

Slave55-relay-bin.XXXXXX

Relay log files record sql commands that generate new data locally

Slave55-relay-bin.index Relay Log File Index File

Relay-log.info records the relay log files currently used by the database server

Master-slave synchronization configuration troubleshooting?

IO thread error: information error in specifying master library from library

Security restrictions (firewall selinux)

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 library or table operated by the command does not exist locally.

View the error message:

Last_SQL_Error: error message

Mysql > stop slave

In a library or table operated by commands from this machine

Mysql > start slave

+ +

Let the slave database temporarily not synchronize the data of the master database?

Mysql > stop slave

Restore from the library to a 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 and one slave

One master and many followers

Master and subordinate

Mutual master and slave (master structure)

Reset master # clear the master's log

Reset slave # empty logs only

Reset slave all # needs to reconfigure change master

+ +

Master-slave synchronization common configuration parameters? (/ etc/my.cnf)

Configuration parameters of the host in the main library role

[mysqld]

Binlog_do_db= database name, database name # only synchronized library binlog_ignore_db= database name, database name # only synchronized libraries

Mysql > show master stauts

Configuration parameters of the slave library role host

[mysqld]

Log_slave_updates cascade replication

Replicate_do_db= database name, database name # synchronized libraries only

Replicate_ignore_db= database name, database name # only unsynchronized library relay_log= file name # set relay log file name

+ +

2. Separation of data reading and writing

2.1 introduction to the separation of reading and writing? The operation of customer query record and the operation of writing data are performed on different database servers.

2.2 Why do you want to separate reading and writing? Reduce the concurrent access pressure of a single database server and provide the utilization of server hardware resources

2.3 configuration data read-write separation (Maxscale + master-slave synchronization structure)

2.3.1 Topology

Client

| |

53 maxscale 4010 management port 4006 read-write separation port

Insert select

Write and check

54 55

Master and slave

2.3.2 configure MySQL one Master one Slave synchronization structure

2.3.3 configure maxscale (53)

a. Pack a bag

b. Modify the configuration file

Vim / etc/maxscale.cnf

9 [maxscale]

10 threads=1

18 [server1]

19 type=server

20 address=192.168.4.54

21 port=3306

22 protocol=MySQLBackend

twenty-three

25 [server2]

26 type=server

27 address=192.168.4.55

28 port=3306

29 protocol=MySQLBackend

38 [MySQL Monitor]

39 type=monitor

40 module=mysqlmon

41 servers=server1, server2

42 user=scalemon # monitors the running status and master-slave status of the database service

43 passwd=123456

44 monitor_interval=10000

66 [Read-Write Service]

67 type=service

68 router=readwritesplit

69 servers=server1, server2

70 user=maxscale # check whether the user name and password of the connection exist on the database server when receiving the client connection request

71 passwd=123456

72 max_slave_connections=100%

78 [MaxAdmin Service]

79 type=service

80 router=cli

94 [Read-Write Listener]

95 type=listener

96 service=Read-Write Service

97 protocol=MySQLClient

98 port=4006

100 [MaxAdmin Listener]

101 type=listener

102 service=MaxAdmin Service

103 protocol=maxscaled

104 socket=default

105 port=4010

Add the corresponding authorized user on the database server according to the settings of the configuration file.

Mysql > grant replication slave, replication client on. To scalemon@'%' identified by

"123456"

Mysql > grant select on mysql.* to maxscale@'%' identified by "123456"

c. Start the service

Systemctl stop mysqld

[root@003] # maxscale-f / etc/maxscale.cnf

[root@003 ~] # netstat-utnalp | grep: 4010

Tcp6 0 0: 4010: LISTEN 9559/maxscale

[root@003 ~] #

[root@003 ~] # netstat-utnalp | grep: 4006

Tcp6 00: 4006: LISTEN 9559/maxscale

[root@003 ~] # netstat-utnalp | grep maxscle

[root@003 ~] # 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 00: 4006: LISTEN 9559/maxscale

[root@003 ~] #

53 access the local management service to view the status of the monitoring host

[root@003] # maxadmin-P4010-uadmin-pmariadb

MaxScale > list servers

Stop the maxscale service

# ps-C maxscale

# kill-9 pid number

d. Test the configuration on the client side

# ping-c 2 192.168.4.53

D.1 add users who access data on the database server

Mysql > grant all on. To student@'%' identified by "123456"

D.2 access to proxy host

# mysql-P4006-h292.168.4.53-ustudent-p123456

+ +

Third, MySQL optimization (optimization ideas, what to optimize, how to optimize)

When accessing data, the result is very slow. Analyze what may have caused it.

1 low hardware configuration: check the utilization of application devices, CPU memory storage (hard disk)

Top 0.0 wa

Sar

Uptime I/O

Free-m

2 network bandwidth: use network speed measurement software for network speed

(3) the software version of providing database service is low:

View the run parameter configuration of the database service runtime (common configuration parameters)

Timeout time

Connect_timeout

The timeout of the three-way handshake when establishing a connection

Timeout for wait_timeout to wait for disconnection after establishing a connection

Mysql > show variables like "connect_timeout"

Mysql > show variables like "wait_timeout"

The number of threads allowed to be reused

Mysql > show variables like "thread_cache_size"

The number of open tables for all thread caches.

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 user order by uid desc; query cache

Name= "lucy"

Pc3 update user set name= "lili" where name= "lucy"

Query_cache_type 0 | 1 | 2

0 turn off do not store

1 enable unconditional storage

2 enable specified storage before storage

Query_cache_limit 1048576 exceeds 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"

Maximum number of connections ever had

Mysql > show global status like "Max_used_connections"

Formula:

Maximum number of connections ever had / concurrent connections = 0.85 * 100% = 85%

3000 / X = 0.85

View the value of the 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

Permanent definition

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 written by the program to query the database is unreasonable, which leads to the slow processing of the database.

Enable slow query logs on the database server: record sql commands that display query results for more than a specified time. (tolerance value 3 seconds)

(5) the data storage architecture has a data transmission bottleneck.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.