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

Steps to configure master-slave synchronization

Environmental preparation

There must be more data in the main database than you do.

Systemctl stop firewalld

Setenforce 0

Physical connection can be reached.

Configure the primary database server

1 user authorization

Grant replication slave on *. * to plj@ "%" identified by "123456"

2 modify the configuration file

Vim / etc/my.cnf

Server_id=12

Log_bin=master12

Log_format= "mixed"

: wq

3 restart data service

Systemctl restart mysqld

4 View the binlog log information currently in use

Mysql-uroot-p123123

Mysql > show master status

Configure from the database server

1 verify the authorized user of the main library

Mysql-h292.168.4.12-uplj-p123456

Mysql >

2 modify the configuration file

Vim / etc/my.cnf

Server_id=11

Log_bin=slave11 # optional

Log_format= "mixed"

: wq

3 restart data service

Systemctl restart mysqld

4 the administrator logs in to designate himself as a slave from the database server

Mysql-uroot-p654321

Mysql > show slave status\ G

Mysql > change master to master_host= "192.168.4.12", master_user= "plj", master_password= "123456", master_log_file= "master12.000001", master_log_pos=154

5 start the slave process

Mysql > start slave

6 View slave process status information

Mysql > show slave status\ G

Slave_io_running: yes

Slave_sql_running: yes

Verify the master-slave synchronization configuration

The client accesses the master database server and creates library table records, which can also be seen on the slave data server

Working principle of master-slave synchronization

Slave_io_running: store the sql in the binlog log on the primary database server into the local relay log file.

Last_IO_Error: error message

Error reason: unable to connect to the master database server from the slave server

Ping firewalld selinux

The configuration information for change master to is incorrect.

Slave_sql_running: execute the sql command in the native relay log file.

Last_SQL_Error: error message

Mysql > stop slave

Mysql > change master to option = value, option = value

Mysql > start slave

Cd / var/lib/mysql/

Information about master.info connecting to the primary database server

Relay-log.info Relay Log File

Localhost-relay-bin.xxxxxx Relay Log File

Localhost-relay-bin.index Relay Log Index File

Restore a database server to a stand-alone database server.

# cd / var/lib/mysql

# rm-rf master.info relay-log.info localhost-relay-bin.*

# systemctl restart mysqld

Let the data be temporarily out of sync from the database server (just start again if you want to)

Mysql > stop slave

+

Day06

Mysql master-slave synchronization structure mode

One master and one slave

One master and many followers

Master and subordinate

Master structure (mutual principal and subordinate)

+ +

Mysql master-slave synchronization configuration parameters

Configuration parameters used by the primary database server

/ etc/my.cnf

[mysqld]

Binlog_do_db= database name, database name # only libraries that allow synchronization

Binlog_ignore_db= database name, database name # only libraries that do not allow synchronization

: wq

# systemctl restart mysqld

Configuration parameters used from the database server

/ etc/my.cnf

[mysqld]

Log_slave_updates # allows cascading replication

Relay_log= name # Relay log file name

Replicate_do_db= database name, database name # synchronized libraries only

Replicate_ignore_db= database name, database name # only unsynchronized libraries

: wq

# systemctl restart mysqld

+ +

Middleware maxscale

Read-write separation (maxscale + mysql master-slave synchronization)

Maxscale-2.1.2-1.rhel.7.x86_64.rpm

It is required to configure the read-write separation service on the host 192.168.4.10, after the client connects

Assign the executed query request to the 192.168.4.11 host, and execute the write request score

Rationing 192.168.4.12 mainframe

Configuration

1 Slave database server with 11 hosts configured as 12

2 run the service maxscale with read-write separation function on host 10

2.1 install the package

Rpm-ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm

2.2 modify the configuration file

# sed-I'/ # / d' / etc/maxscale.cnf

# vim / etc/maxscale.cnf

[root@localhost ~] # cat / etc/maxscale.cnf

[maxscale]

Threads=1

[server1]

Type=server

Address=192.168.4.12

Port=3306

Protocol=MySQLBackend

[server2]

Type=server

Address=192.168.4.11

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=6606

[root@localhost ~] #

Profile description

0 sets the number of threads started by the service run

[maxscale]

1 Database server member [server1] [server2]

2 Monitoring database server member list [MySQL Monitor]

User=scalemon / / user name

Passwd=111111 / / password

3 specify read-write separation between those database servers

[Read-Write Service]

User=maxscale / / user name

Passwd=111111 / / password

4 define management services

[MaxAdmin Service]

5 Port number monitored by read-write separation service

[Read-Write Listener]

6 specify the port number to be monitored by the management service

[MaxAdmin Listener]

2.2 add authorized users according to the settings of the profile (12 11)

Scalemon user

Grant replication slave, replication client on *. * to scalemon@ "%" identified by "111111"; / / create monitoring users

Maxscale user

Grant select on mysql.* to maxscale@'%' identified by "111111"; / / create a routing user

2.4 start maxscale

# maxscale-f / etc/maxscale.cnf

# netstat-utnalp | grep: 4006

# netstat-utnalp | grep: 6606

# maxadmin-uadmin-pmariadb-P6606

MaxScale > list servers

Servers.

-+-

Server | Address | Port | Connections | Status

-+-

Server1 | 192.168.4.12 | 3306 | 0 | Master, Running

Server2 | 192.168.4.11 | 3306 | 0 | Slave, Running

-+-

MaxScale > quit

[root@localhost ~] #

+

The connection user used by the client to access data on the master-slave database server

Mysql > grant all on *. * to student@ "%" identified by

"123456"

Client access

# which mysql

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

Mysql > select-> 11 from

Mysql > insert-> 12 main

+

Mysql performance tuning

What may be the reasons why the database server processes the access speed of the client very slowly?

Caused by it?

1 insufficient network bandwidth

2 the server configuration is too low

3 provide database service software version is low

View the run parameters (variables) of the service runtime

Mysql-uroot-p123123

Mysql > show variables like 'keyword'

Mysql > set [GLOBAL] variable name = value

Vim / etc/my.cnf

[mysqld]

Variable = value

: wq

Number of concurrent connections

Max_connections

Mysql > show global status like "max_used_conn%"

Max_used_connections/max_connections=0.85 * 100%

Connection timeout

Mysql > show variables like "timeout%"

Connect_timeout=10

Wait_timeout=28800

Allows you to save the number of threads reused in the cache

Thread_cache_size=10

The number of open tables cached for all threads

Table_open_cache = 3

Query cache parameter settings?

Mysql > show variables like "query_cache%"

Query_cache_type=0/1/2

1 select * from T1; show global status like "qcache%"

4 there is something wrong with the programmer's programming program (accessing data is caused by the complexity of sql commands written.

Slow speed)

The mysql service supports four types of log files:

Error log binlog query log slow query log

Enabled by default

Query log: all sql commands executed by the client after connecting to the server.

General-log

General-log-file= directory / name

Default storage location and name

Cat / var/lib/mysql/ hostname.log

Slow log: after the client connects to the server, the record exceeds the specified time (default

10 seconds) the sql command that displays the query results.

Slow-query-log

Slow-query-log-file= directory / name

Long-query-time=3

Default storage location and name

Mysql > select sleep (11)

Cat / var/lib/mysql/ hostname-slow.log

# mysqldumpslow localhost-slow.log > / tmp/sql.err

4 there is something wrong with the programmer's programming program (accessing data is caused by the complexity of sql commands written, which leads to slow processing)

Enable slow query logs to record sql commands that display query results beyond a specified time

5 there is something wrong with the network structure

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.