MySQL Advanced simple parsing

MySQL Advanced 1.mysql configuration file

The MySQL configuration file is/etc/my.cnf
Configuration file Lookup Order: If there is a setting in multiple profiles, the last found final effect

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FI  LE --> ~/.my.cnf

MySQL Common configuration file parameters:

Parameters	Description
Port	Setting the Listening port
Socket=/tmp/mysql.sock	Specify socket file Location
Basedir=/usr/local/mysql	Specify the path where MySQL data is stored
Pid-file=/data/mysql/mysql.pid	Specify the process ID file storage path
User=mysql	Specify the identity of MySQL to provide services
Skip-name-resolve	The time that MySQL is prevented from DNS resolution of external connections. If this option is turned on, all remote host connection authorizations must use the IP address method otherwise MySQL will not be able to handle the connection request properly

2.mysql Database Backup 2.1 database common backup scheme

Database backup Scenario:

• Full-scale backup
• Incremental backup
• Differential backup

Backup Scenarios	features
Differential backup	Backs up all files that have changed since the last full backup. A differential backup is a backup of a file that is added or modified after a backup is made to a differential backup during the time period. When recovering, we only need to recover the first full-time and last differential backups.
Full-scale backup	Full-scale backup means a full copy of all data or applications that are eaten at a certain point in time. Data recovery fast backup time is long
Incremental backup	Incremental backup means that after a full or last incremental backup, each backup only needs to be backed up with the files that were added or modified compared to the previous one. This means that the object of the first incremental backup is the addition and modification of the file that is generated after a backup, and so on. Short recovery data must be performed in a certain order without duplicate backup data backup

2.2mysql Backup Tool mysqldump

//语法：mysqldump [OPTIONS] database [tables ...]mysqldump [OPTIONS] --all-databases [OPTIONS]mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

Common options:

-uUSERNAME  //指定数据库用户名-hHOST     //指定服务器主机名，请使用ip地址-pPASSWORD  //指定数据库用户的密码-P#         //指定数据库监听的端口，这里的#需要用实际的端口号代替

Backing up the entire database (fully prepared)

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || linfan             || mysql              || performance_schema || sys                |+--------------------+5 rows in set (0.00 sec)mysql> show tables;+------------------+| Tables_in_linfan |+------------------+| doudou           || linfan           |+------------------+2 rows in set (0.00 sec)[[email protected] ~]# lsanaconda-ks.cfg  doudou.repo  lin  nfs.sh[[email protected] ~]# mysqldump -uroot -p -h127.0.0.1 --all-databases > all-20180818.sqlEnter password:[[email protected] ~]# lsall-20180818.sql  anaconda-ks.cfg  doudou.repo  lin  nfs.sh

Back up the Doudou table for the Linfan library

[[email protected] ~]# mysqldump -uroot -p -h127.0.0.1 linfan doudou > tabledoudou-20180818.sqlEnter password:[[email protected] ~]# lsall-20180818.sql  anaconda-ks.cfg  doudou.repo  lin  nfs.sh  

Backing Up the Linfan database

[[email protected] ~]# mysqldump -uroot -p -h127.0.0.1 --databases linfan > data-linfan-20180818.sqlEnter password:[[email protected] ~]# lsall-20180818.sql  anaconda-ks.cfg  data-linfan-20180818.sql  doudou.repo  lin  nfs.sh  tabledoudou-20180818.sql

Simulated mis-deletion of Linfan database

mysql> drop  database linfan ;Query OK, 2 rows affected (0.02 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.01 sec)

2.3 MySQL Data recovery

[[email protected] ~]# lsall-20180818.sql  anaconda-ks.cfg  data-linfan-20180818.sql  doudou.repo  lin  nfs.sh  tabledoudou-20180818.sql[[email protected] ~]# mysql -uroot -p -h127.0.0.1 < all-20180818.sqlEnter password:[[email protected] ~]# mysql -uroot -p -h127.0.0.1 -e ‘show databases;‘Enter password:+--------------------+| Database           |+--------------------+| information_schema || linfan             || mysql              || performance_schema || sys                |+--------------------+

Recovering the Doudou table for the Linfan database

Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> source tabledoudou-20180818.sql; mysql> show tables;+------------------+| Tables_in_linfan |+------------------+| doudou           || linfan           |+------------------+2 rows in set (0.00 sec)

Simulate deleting an entire database

mysql> show tables;+------------------+| Tables_in_linfan |+------------------+| doudou           || linfan           |+------------------+2 rows in set (0.00 sec)mysql> show database;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘database‘ at line 1mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || linfan             || mysql              || performance_schema || sys                |+--------------------+5 rows in set (0.00 sec)

Recovering an entire database

[[email protected] ~]# lsall-20180818.sql  anaconda-ks.cfg  data-linfan-20180818.sql  doudou.repo  lin  nfs.sh  tabledoudou-20180818.sql[[email protected] ~]# mysql -uroot -p -h127.0.0.1 < all-20180818.sqlEnter password:[[email protected] ~]# mysql -uroot -p -h127.0.0.1  -e ‘show databases;‘Enter password:+--------------------+| Database           |+--------------------+| information_schema || linfan             || mysql              || performance_schema || sys                |+--------------------+

3. mysql installation in binary format

Download MySQL package in binary format

[[email protected] ~]# cd /usr/src/[[email protected] src]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz  

Create users and Groups

[[email protected] ~]# groupadd -r mysql[[email protected] ~]# useradd -M -s /sbin/nologin -g mysql mysql

Extract software to/usr/local/

[[email protected] src]# lsapr-1.6.3 apr-util-1.6.1 Debug Mysql-5.7.22-linux-glibc2.12-x86_64.ta r.gzapr-1.6.3.tar.bz2 apr-util-1.6.1.tar.bz2 kernels[[email protected] src]# tar XF  Mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz-c/usr/local/[[email protected] src]# ls/usr/local/apache apr-util                                 etc include lib64 mysql-5.7.22-linux-glibc2.12-x86_64 Shareapr bin games Lib Libexec sbin Src[[email protected] src]# cd/usr/local/[[email protected] local]# ln-sv mysql-5.7 .22-linux-glibc2.12-x86_64/mysql ' MySQL ', ' mysql-5.7.22-linux-glibc2.12-x86_64/' [[[email protected] Local ]# lltotal 0drwxr-xr-x.  Root root 12:46 apachedrwxr-xr-x.  6 root root 12:35 aprdrwxr-xr-x.  5 root root 12:40 apr-utildrwxr-xr-x.  2 root root 6 Nov 5 bindrwxr-xr-x.  2 root root 6 Nov 5 etcdrwxr-xr-x.  2 root root 6 Nov 5 gamesdrwxr-xr-x. 2 roOT Root 6 Nov 5 includedrwxr-xr-x.  2 root root 6 Nov 5 libdrwxr-xr-x.  2 root root 6 Nov 5 lib64drwxr-xr-x.  2 root root 6 Nov 5 libexeclrwxrwxrwx.  1 root root 13:54 mysql---mysql-5.7.22-linux-glibc2.12-x86_64/drwxr-xr-x.  9 root root 129 13:30 mysql-5.7.22-linux-glibc2.12-x86_64drwxr-xr-x.  2 root root 6 Nov 5 sbindrwxr-xr-x.  5 root root 15:44 sharedrwxr-xr-x. 2 root root 6 Nov 5 src

Modify the genus/usr/locaal/mysql of the directory

[[email protected] ~]# chown -R mysql.mysql /usr/local/mysql[[email protected] ~]# ll /usr/local/mysql -dlrwxrwxrwx. 1 mysql mysql 36 Aug 17 13:54 /usr/local/mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/

Adding environment variables

[[email protected] ~]# ls /usr/local/mysqlbin  COPYING  docs  include  lib  man  README  share  support-files[[email protected] ~]# echo ‘export PATH=/usr/local/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh[[email protected] ~]# . /etc/profile.d/mysql.sh[[email protected] ~]# echo $PATH/usr/local/mysql/bin:/usr/local/apache/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

Set up a data storage directory

[[email protected] ~]# cd /usr/local/mysql[[email protected] mysql]# mkdir /opt/data[[email protected] mysql]# chown -R mysql.mysql /opt/data/[[email protected] mysql]# ll /opt/total 0drwxr-xr-x. 2 mysql mysql   6 Aug 17 14:05 datadrwxr-xr-x. 8 root  root  220 Jul 18 17:09 lin.d

Initializing the database

  [[email protected] mysql]#/usr/local/mysql/bin/mysqld--initialize--user=mysql--datadir=/opt/ data/2018-08-17t06:08:33.347313z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. --explicit_defaults_for_timestamp server option (see documentation for more details). 2018-08-17t06:0 8:33.873415z 0 [Warning] innodb:new log files created, lsn=457902018-08-17t06:08:33.953310z 0 [Warning] innodb:creating  FOREIGN KEY constraint system tables.2018-08-17t06:08:34.016549z 0 [Warning] No existing UUID have been found, so we assume That's the first time that this server has been started. Generating a new uuid:f8e46285-a1e3-11e8-b6bf-000c29c9d4ed.2018-08-17t06:08:34.019542z 0 [Warning] Gtid table is not rea Dy to be used. Table ' mysql.gtid_executed ' cannot be opened.2018-08-17t06:08:34.023380z 1 [Note] A temporary password are generated for [E Mail protected]: b 

Build configuration file

[[email protected] ~]# cat > /etc/my.cnf <<EOF> [mysqld]> basedir = /usr/local/mysql> datadir = /opt/data> socket = /tmp/mysql.sock> port = 3306> pid-file = /opt/data/mysql.pid> user = mysql> skip-name-resolve> EOF

Configure the service startup script

[[email protected] ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld[[email protected] ~]# sed -ri ‘s#^(basedir=).*#\1/usr/local/mysql#g‘ /etc/init.d/mysqld[[email protected] ~]# sed -ri ‘s#^(datadir=).*#\1/opt/data#g‘ /etc/init.d/mysqld 

Start MySQL

[[email protected] ~]# service mysqld startstarting mysql.logging to '/opt/data/linfan.err '. success! [[email protected] ~]# ps-ef|grep mysqlroot 52200 1 0 14:25 pts/1 00:00:00/bin/sh/usr/local/mysql/bi N/mysqld_safe--datadir=/opt/data--pid-file=/opt/data/mysql.pidmysql 52378 52200 4 14:25 pts/1 00:00:00/usr/loc Al/mysql/bin/mysqld--basedir=/usr/local/mysql--datadir=/opt/data--plugin-dir=/usr/local/mysql/lib/plugin--user =mysql--log-error=linfan.err--pid-file=/opt/data/mysql.pid--socket=/tmp/mysql.sock--port=3306root 52408 2998 0 14:25 pts/1 00:00:00 grep--color=auto mysql[[email protected] ~]# ss-antlstate recv-q Send-Q Local Address:port Peer address:portlisten 0 128 *: *:* LISTEN 0 100 127.0.0.1:25 *:* LISTEN 0LISTEN::: +:::* 0 128 ::: +:::* LISTEN 0 100:: 1:25 :::* LISTEN 0 80::: 3306 :::*

Change Password
Modify with temporary password

[[email protected] ~]# mysql -uroot -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.22Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> set password = password(‘linfan123‘);Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> quitBye

MySQL Advanced simple parsing