Day 12 of MySQL Introduction (database configuration and deployment)


12.1MySQL configuration file

(1) Configuration section

(2) Basic configuration

(3) Memory and optimized configuration

(4) Log configuration

12.2 data backup and restore

(1) Data backup

(2) Data restore

(3) Binary log

12.3 multi instance deployment

12.4 master slave replication

12.1MySQL configuration file

(1) Configuration section

MySQL configuration file my Section of CNF:

[mysqld]: MySQL service configuration

[Client]: configuration of client

(2) Basic configuration

MySQL server basic configuration

SQL mode

Default character set

Default proofing set

Default storage engine

sql_mode: Server SQL mode, which is used to define the SQL syntax supported by MySQL and which data validation check to perform. Unless there are special requirements, configuration is generally not required, and the default value can be used.

character_set_server: the default character set of the server, which is latin1 by default.

collation_server: the default proofreading set of the server, which is Latin1 by default_ swedish_ ci.

explicit_defaults_for_timestamp: how the server handles the default value and NULL value of the TIMESTAMP column. The default value is 0. When set to 1, the TIMESTAMP column can hold NULL values.

max_connections: the maximum number of simultaneous connections allowed by the client. The default is 151. open_files_limit: the number of files that the operating system allows mysqld to open. The default is 5000.

default_storage_engine: the default storage engine, which is InnoDB by default.

(3) Memory and optimized configuration

key_buffer_size: the size of the index buffer, which is shared by all threads. Increasing the size can get a better index, and the default value is 8M.

table_open_cache: the number of caches of open tables of all threads. It is used to access table contents faster. The default is 2000.

sort_buffer_size: the buffer size allocated for each session to be sorted. The default is 256K.

read_buffer_size: the buffer allocated by each thread that performs sequential scanning on the MyISAM table. The default is 128K.

thread_cache_size: how many threads should the server cache for reuse? The default is - 1 (auto resize).

query_cache_size: indicates the size of the query buffer. The default is 1M.

read_buffer_size: the buffer allocated by each thread that performs sequential scanning on the MyISAM table. The default is 128K.

thread_cache_size: how many threads should the server cache for reuse? The default is - 1 (auto resize).

query_cache_size: indicates the size of the query buffer. The default is 1M.

(4) Log configuration

Classification of MySQL logs:

Error log

General log

Binary log

Slow query log

Log error: the file path of the error log, which saves the log information when MySQL starts, runs or stops.

General log: whether to enable the general log, which is used to record the SQL statements connected and executed by the client.

general-log_file: the file path of the general log.

log_slow_queries: slow query log file path, used to record more than long_query_time or queries that do not use indexes.

long_query_time: when the query time exceeds the specified value, it will be recorded in the slow query log.

log_queries_not_using_indexes: whether to record queries without indexes in the slow query log.

Log Bin: the storage path of binary logs, which is mainly used for replication environment and data recovery.

max_binlog_size: the size limit of a single binary log file.

expire_logs_days: automatically clear expired logs that exceed the specified number of days.

12.2 data backup and restore

(1) Data backup

The mysqldump tool supports backing up single or multiple databases.

Syntax format for backing up a single database:

mysqldump -uusername -ppassword dbname [tbname1 [tbname2...]]

Backup multiple databases syntax format:

mysqldump -uusername -ppassword --databases dbname1 [dbname2 ...] 

Backup all databases syntax format:

mysqldump -uusername -ppassword --all-databases

Save output results:

The mysqldump command will output the results directly to the screen. In order to save the output results, you need to add "> filename.sql" after the command to redirect the output to filename SQL file (the path can be added before the file name).

(2) Data restore

1. The mysql dump command can use input redirection to read the SQL script to restore data, and add "< filename. SQL" at the end of the mysql command.

mysql -uusername -ppassword [dbname] <filename.sql

[dbname]: if the SQL script contains a command to select a database, it does not need to be specified.

Import a backup of a single database: you need to specify [dbname].

Import backups of multiple databases: there is no need to specify [dbname].

2. Use the MySQL client command source.

source File path

(3) Binary log

Binary log: records the changes of MySQL database.

Such as the creation of tables and the change of table data.

It is used to record the SQL involved in modification, row change of data modification, execution time and other information

Binary log function: build replication architecture (such as master-slave replication), data recovery and other scenarios.

Open binary log:

In my Add configuration in the "[mysqld]" section of CNF configuration file.


Log bin = binlog: enable binary log, and the file name is binlog.

Server id: server id (used to distinguish servers in replication Architecture)

Restart MySQL service to make the configuration effective

[root@localhost ~]# systemctl restart mysqld

Check the opening of binary log

mysql> SHOW VARIABLES LIKE 'log_bin%';
| Variable_name                   | Value                       |
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
5 rows in set (0.00 sec)

View binary log files

[root@localhost ~]# ls /var/lib/mysql | grep binlog

View index file

[root@localhost ~]# cat /var/lib/mysql/binlog.index

binlog. 00000 1 file saves the contents of binary log.

Naming rules for binary log files:

MySQL Service restart
File size reached max_binlog_size Upper limit of configuration (default is 1GB )

If one of the conditions is met, a new log file will be created,

The extension of the new log file is incremented by 1, that is, binlog 000002.

View binary log files

| Log_name      | File_size |
| binlog.000001 |       154 |
1 row in set (0.00 sec)

Log_name: log file name

File_size: file size

To view binary logs, you need to convert SQL scripts into text format

[root@localhost ~]# cd /usr/local/mysql/bin
[root@localhost bin]# ./mysqlbinlog /var/lib/mysql/binlog.000001
# at 123
#180723  5:35:55 server id 1  end_log_pos 154 CRC32 0x7e392ba9 
 ︙((some display results are omitted here)

Import conversion results into MySQL

[root@localhost bin]# ./mysqlbinlog /var/lib/mysql/binlog.000001 | 
mysql -uroot -p123456

Pipe symbol "|": used to take the output result of the command on the left as the input of the command on the right.

Advantages of binary log backup:

When the amount of data in the database is very large, if the method of full backup is adopted, the amount of data backed up each time will be very large and the speed will be very slow.

Binary log records the changes that have taken place in the database. Regular backup of binary log files can achieve the effect of incremental backup.

The mysqlbinlog command also has some options.

mysqlbinlog [options] log-files
• log-files : log file path
• options : Optional options.

Common options:

--Start date and -- stop date: specify the start date and end period.

--Start position and -- stop position: specify the start position and end position.

To restore to the specified location:

You can view it through "at location" in the output log results,

You can also use SHOW MASTER STATUS to view the current location.

Pause binary log

mysql> SET sql_log_bin = 0;

Only valid for the current session. After exiting the MySQL client,

The record will be restored the next time you log in.

Recover binary log records immediately

mysql> SET sql_log_bin = 1;

Delete binary log

# Method 1: delete all binary log files
# Method 2: delete the creation time earlier than binlog All log files of 00000 2
mysql> PURGE MASTER LOGS TO 'binlog.000002';
# Method 3 delete binary logs before July 23, 2018
mysql> PURGE MASTER LOGS BEFORE '20180723';

Method 2: do not delete binlog 00000 2 file itself, method 3 does not delete binlog 00000 2 document itself

View deletion result: SHOW BINARY LOGS

12.3 multi instance deployment

What is multi instance

Running multiple MySQL services in one server can be distinguished by listening to different port numbers.

Significance of multi instance deployment:

When a server has surplus resources, it can use these resources to provide more services through multi instance deployment.

Change profile

[root@localhost ~]# vi /etc/my.cnf

Add the following configuration


Initialize the database for the two new instances

[root@localhost ~]# cd /usr/local/mysql/bin
[root@localhost bin]# ./mysqld --initialize-insecure --datadir=/var/lib/mysql-replica01
[root@localhost bin]# ./mysqld --initialize-insecure --datadir=/var/lib/mysql-replica02

Copy service management script

# ① Switch to the scripts directory in the MySQL source code directory
[root@localhost bin]# cd ~/mysql-5.7.22/scripts
# ② Copy script file
[root@localhost scripts]# cp mysqld@.service /usr/lib/systemd/system

Start multi instance service

# ① Start instance mysqld@replica01 and mysqld@replica02
[root@localhost bin]# systemctl start mysqld@replica01
[root@localhost bin]# systemctl start mysqld@replica02
# ② Set automatic startup
[root@localhost bin]# systemctl enable mysqld@replica01
[root@localhost bin]# systemctl enable mysqld@replica02

View the listening port number

[root@localhost bin]# ss -tnlp | grep mysql
LISTEN   0   80   :::3306    :::*   users:(("mysqld",pid=2709,fd=21))
LISTEN   0   80   :::3307    :::*   users:(("mysqld",pid=2820,fd=21))
LISTEN   0   80   :::3308    :::*   users:(("mysqld",pid=10534,fd=21))

Log in and set password

# ① Login to port 3307 mysqld@replica01 example
[root@localhost bin]# mysql -h127.0.0.1 -P3307 -uroot
# ② Set password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql> exit
# ③ Login to port 3308 mysqld@replica02 example
[root@localhost bin]# mysql -h127.0.0.1 -P3308 -uroot
# ④ Set password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql> exit

12.4 master slave replication

Master slave Replication:

Copy data from one MySQL Master to another

Or multiple MySQL slave servers (Sales).

Implementation principle of master-slave replication:

Through binary log, the binary log of the master server is sent to the slave server for execution,

Synchronize the data from the slave server with the master server.

Advantages of master-slave replication:

The slave server can share the pressure of the master server, and the slave server can be used as the backup of the master server to prevent data loss due to the failure of one of the servers.

Set up a master-slave replication environment:

Master server mysqld@replica01

Slave server mysqld@replica02

Change profile

[root@localhost ~]# vi /etc/my.cnf

Disposition[ mysqld@replica01 ]


Disposition[ mysqld@replica02 ]


Restart the service for the configuration to take effect

[root@localhost ~]# systemctl restart mysqld@replica01
[root@localhost ~]# systemctl restart mysqld@replica02

Log in to the primary server

root@localhost ~]# mysql -h127.0.0.1 -P3307 -uroot -p123456

Create a user slave for accessing from the server with a password of 123456

# ① Create slave user
mysql> CREATE USER 'slave'@'' IDENTIFIED BY '123456';
# ② Give the user the permission of REPLICATION SLAVE
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'';

View the current binary log status of the primary server

| File          | Position   | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| binlog.000001 |        623 |              |                  |                   |
1 row in set (0.00 sec)

Write down the results and exit the main server

mysql> exit

Login from server

[root@localhost ~]# mysql -h127.0.0.1 -P3308 -uroot -p123456

Automatically synchronize master server from server

# ① Specify the information for the primary server
    -> MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=623;
# ② Start synchronization with slave user
mysql> START SLAVE USER='slave' PASSWORD='123456';

CHANGE MASTER TO: change the parameters of connecting from the server to the master server.

MASTER_HOST: the host of the master server.

MASTER_PORT: the port number of the primary server.

MASTER_LOG_FILE: binary log file of the primary server.

MASTER_LOG_POS: the location of the primary server.

USER and PASSWORD: the USER name and PASSWORD to access the master server.

View synchronization status from server

*************************** 1. row ***************************
       Slave_IO_State: Waiting for master to send event
          Master_Host:		# Host of master server
          Master_User: slave			# User name of the primary server
          Master_Port: 3307			# Port number of the primary server
        Connect_Retry: 60			# Connection failed retry seconds
      Master_Log_File: binlog.000001		# Primary server binary log file
  Read_Master_Log_Pos: 623			# Log read location of primary server
       Relay_Log_File: localhost-relay-bin.000002	# Relay log file from server
        Relay_Log_Pos: 319			# The location of the relay log from the server
Relay_Master_Log_File: binlog.000001		# Relay log file of master server
     Slave_IO_Running: Yes			# Is the slave server IO thread running
    Slave_SQL_Running: Yes			# Whether the SQL thread from the server is running
 ︙((some display results are omitted here)
1 row in set (0.00 sec)

Exit from server

mysql> exit

Write data in the master server to test whether the slave server is synchronized

# ① Log in to the primary server
[root@localhost ~]# mysql -h127.0.0.1 -P3307 -uroot -p123456
# ② Create mydb database and test data table, and insert test data
mysql> CREATE DATABASE mydb;
mysql> CREATE TABLE mydb.test (id INT);
mysql> INSERT INTO mydb.test VALUES(1);
# ③ Exit master server
mysql> exit
# ④ Login from server
[root@localhost ~]# mysql -h127.0.0.1 -P3308 -uroot -p123456
# ⑤ Reading data from the server
mysql> SELECT * FROM mydb.test \G
*************************** 1. row ***************************
id: 1
1 row in set (0.00 sec)

When data is written from the slave server, the master server will not synchronize

# ① Write data from server in
mysql> INSERT INTO mydb.test VALUES(2);
# ② Exit from server
mysql> exit
# ③ Log in to the primary server
[root@localhost ~]# mysql -h127.0.0.1 -P3307 -uroot -p123456
# ④ Read data from the master server
mysql> SELECT * FROM mydb.test\G
*************************** 1. row ***************************
id: 1
1 row in set (0.00 sec)

Keywords: Linux Database MySQL CentOS

Added by sun14php on Sun, 20 Feb 2022 16:11:48 +0200