catalogue
(3) Memory and optimized configuration
12.3 multi instance deployment
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.
[mysqld] ...... log-bin=binlog server-id=1
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 binlog.000001 binlog.index
View index file
[root@localhost ~]# cat /var/lib/mysql/binlog.index ./binlog.000001
binlog. 00000 1 file saves the contents of binary log.
Naming rules for binary log files:
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
mysql> SHOW BINARY LOGS; +---------------+-----------+ | 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 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ROLLBACK/*!*/; BINLOG ' u1VSWw8BAAAAdwAAAHsAAAABAAQANS43LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAA AAAACgoKKioAEjQAAaQ8NVo= '/*!*/; # 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
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 mysql> RESET MASTER; # 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
[mysqld@replica01] port=3307 datadir=/var/lib/mysql-replica01 socket=/var/lib/mysql-replica01/mysql.sock log-error=/var/lib/mysql-replica01/mysqld.log [mysqld@replica02] port=3308 datadir=/var/lib/mysql-replica02 socket=/var/lib/mysql-replica02/mysql.sock log-error=/var/lib/mysql-replica02/mysqld.log
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 ]
log-bin=binlog server-id=2
Disposition[ mysqld@replica02 ]
server-id=3
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'@'127.0.0.1' IDENTIFIED BY '123456'; # ② Give the user the permission of REPLICATION SLAVE mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'127.0.0.1';
View the current binary log status of the primary server
mysql> SHOW MASTER STATUS; +---------------+------------+--------------+------------------+-------------------+ | 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
mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3307,
-> 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
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1 # 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)