[root@lzz ~]# wget https://downloads.mysql.com/archives/get/p/2 three/file/mysql-five.7.33-linux-glibc2.12-x8 six_6 four.tar.gz --2021-05-05 23:42:16-- https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz Resolving host downloads.mysql.com (downloads.mysql.com)... 137.254.60.14 on connection downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:443... Connected. Issued HTTP Request, waiting for response... 302 Found Location: https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz [follow to new URL] --2021-05-05 23:42:17-- https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz Resolving host cdn.mysql.com (cdn.mysql.com)... 184.26.84.248 on connection cdn.mysql.com (cdn.mysql.com)|184.26.84.248|:443... Connected. Issued HTTP Request, waiting for response... 200 OK Length: 661718255 (631M) [application/x-tar-gz] Saving to: "mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz" mysql-5.7.33-linux-gli 100%[==========================>] 631.06M 2.99MB/s Time 3 m 34s 2021-05-05 23:45:52 (2.95 MB/s) - Saved“ mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz" [661718255/661718255])
2. User related!
[root@lzz ~]# useradd -r -M -s /sbin/nologin mysql (create user!)
[root@lzz ~]# tar xf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /usr/local
(Specify the decompression directory!)
[root@lzz ~]# cd /usr/local
[root@lzz local]# ls
bin games lib libexec sbin src
etc include lib64 mysql-5.7.33-linux-glibc2.12-x86_64 share
[root@lzz local]# mv mysql-5.7.33-linux-glibc2.12-x86_64/ mysql (change the directory name to simple mode)
[root@lzz local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
[root@lzz local]# chown -R mysql.mysql /usr/local/mysql (modify corresponding permissions!)
[root@lzz local]# ll
Total consumption 0
drwxr-xr-x. 2 root root 6 5 June 19, 2020 bin
drwxr-xr-x. 2 root root 6 5 June 19, 2020 etc
drwxr-xr-x. 2 root root 6 5 June 19, 2020 games
drwxr-xr-x. 2 root root 6 5 June 19, 2020 include
drwxr-xr-x. 2 root root 6 5 June 19, 2020 lib
drwxr-xr-x. 3 root root 17 4 October 29:14 lib64
drwxr-xr-x. 2 root root 6 5 June 19, 2020 libexec
drwxr-xr-x. 9 mysql mysql 129 5 June 00:26 mysql
drwxr-xr-x. 2 root root 6 5 June 19, 2020 sbin
drwxr-xr-x. 5 root root 49 4 October 29:14 share
drwxr-xr-x. 2 root root 6 5 June 19, 2020 src
[root@lzz local]# vim /etc/profile.d/mysql.sh (set environment variable!)
[root@lzz local]# . /etc/profile.d/mysql.sh
[root@lzz local]# which mysql
/usr/local/mysql/bin/mysql
[root@lzz local]# cat /etc/profile.d/mysql.sh
PATH=/usr/local/mysql/bin:$PATH
[root@lzz local]# mkdir /opt/date (create a directory for storing data!)
[root@lzz local]# chown -R mysql.mysql /opt/date
[root@lzz local]# ll /opt
Total consumption 0
drwxr-xr-x. 2 mysql mysql 6 5 June 00:37 date
3. Initialize to get and save the password!
[root@lzz local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/ 2021-05-05T20:58:43.600829Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2021-05-05T20:58:43.839479Z 0 [Warning] InnoDB: New log files created, LSN=45790 2021-05-05T20:58:43.877654Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2021-05-05T20:58:43.ninety-three 5239Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ad7737af-ade4-11eb-9e4c-000c2973034c. 2021-05-05T20:58:43.936374Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2021-05-05T20:58:44.696457Z 0 [Warning] CA certificate ca.pem is self signed. 2021-05-05T20:58:44.822501Z 1 [Note] A temporary password is generated for root@localhost: TwZp_gu0n=_; [root@lzz ~]# vim password [root@lzz ~]# cat password TwZp_gu0n=_;
4. Input configuration file!
[root@lzz ~]# vim /etc/my.cnf [root@lzz ~]# cat /etc/my.cnf [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
5. Configure startup script and startup self startup!
[root@lzz ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@lzz ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@lzz ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld
[root@lzz ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/lzz.err'.
SUCCESS!
[root@lzz ~]# chkconfig mysqld on
[root@lzz ~]# chkconfig --list
Note: the output result is only displayed SysV Service not included
Primordial systemd Service. SysV Configuration data
May be native systemd Configure overrides.
To list systemd Service, please execute 'systemctl list-unit-files'.
View in detail target Please execute the following steps to enable the service:
'systemctl list-dependencies [target]'.
mysqld 0:shut 1:shut 2:open 3:open 4:open 5:open 6:shut
[root@lzz ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:111 [::]:* 6. Header library file configuration!
[root@lzz ~]# ln -s /usr/local/mysql/include /usr/include/mysql [root@lzz ~]# vim /etc/ld.so.conf.d/mysql.conf [root@lzz ~]# cat /etc/ld.so.conf.d/mysql.conf /usr/local/mysql/lib [root@lzz ~]# ldconfig
7. Start MySQL and configure a new password!
[root@lzz ~]# mysql -uroot -p
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
[root@lzz ~]# dnf provides libncurses.so.5
Last metadata expiration check: 0:26:11 Before, it was executed at 16:48:52 on Wednesday, May 5, 2021.
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
Warehouse : baseos
Matching source:
provide : libncurses.so.5
[root@lzz ~]# dnf -y install ncurses-compat-libs
Last metadata expiration check: 0:27:13 Before, it was executed at 16:48:52 on Wednesday, May 5, 2021.
Dependency resolution.
==========================================================================================
software package framework edition Warehouse size
==========================================================================================
install:
ncurses-compat-libs x86_64 6.1-7.20180224.el8 baseos 331 k
Transaction summary
==========================================================================================
Install 1 package
Total Download: 331 k
Installation size: 1.2 M
Download package:
ncurses-compat-libs-6.1-7.20180224.el8.x86_64.rpm 108 kB/s | 331 kB 00:03
------------------------------------------------------------------------------------------
total 93 kB/s | 331 kB 00:03
Warning:/var/cache/dnf/baseos-055ffcb2ec25a27f/packages/ncurses-compat-libs-6.1-7.20180224.el8.x86_64.rpm: head V3 RSA/SHA256 Signature, secret key ID 8483c65d: NOKEY
CentOS Stream 8 - BaseOS 1.6 MB/s | 1.6 kB 00:00
Import GPG Public key 0 x8483C65D:
Userid: "CentOS (CentOS Official Signing Key) <security@centos.org>"
fingerprint: 99DB 70FA E1D7 CE22 7FB6 4882 05B5 55B3 8483 C65D
come from: /etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
Import public key succeeded
Run transaction check
Transaction check succeeded.
Run transaction test
Transaction test succeeded.
Run transaction
In preparation : 1/1
install : ncurses-compat-libs-6.1-7.20180224.el8.x86_64 1/1
Run script: ncurses-compat-libs-6.1-7.20180224.el8.x86_64 1/1
verification : ncurses-compat-libs-6.1-7.20180224.el8.x86_64 1/1
Installed products updated.
already installed:
ncurses-compat-libs-6.1-7.20180224.el8.x86_64
complete!
[root@lzz ~]# cat password
TwZp_gu0n=_;
[root@lzz ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password = password('123liuZEzheng!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
8. mysql configuration file
The mysql configuration file is / etc / my cnf
Search order of configuration file: if it is set in multiple configuration files, the last found will take effect
/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf
mysql common configuration file parameters:
| parameter | explain |
|---|---|
| port = 3306 | Set listening port |
| socket = /tmp/mysql.sock | Specify socket file location |
| basedir = /usr/local/mysql | Specify the installation path of MySQL |
| datadir = /data/mysql | Specify the data storage path of MySQL |
| pid-file = /data/mysql/mysql.pid | Specify the storage path of process ID file |
| user = mysql | Specify which user MySQL provides services as |
| skip-name-resolve | Prohibit MySQL from DNS resolution for external connections Using this option can eliminate the time for MySQL to perform DNS resolution. If this option is enabled, all remote host connection authorizations must use the IP address party Otherwise, MySQL will not be able to process the connection request normally |
9. mysql database backup and recovery
9.1 common database backup schemes
Database backup scheme:
- Full backup
- Incremental backup
- Differential backup
| Backup scheme | characteristic |
|---|---|
| Full backup | Full backup refers to a full copy of all data or applications at a certain point in time. Fast data recovery. Long backup time |
| Incremental backup | Incremental backup means that after a full backup or the last incremental backup, each subsequent backup only needs to be backed up The number of people who have been modified is increased compared with the previous one. This means that objects are backed up incrementally for the first time It refers to the documents added and modified after full preparation; The object of the second incremental backup is the first incremental backup The added and modified files generated after backup, and so on. No duplicate backup data Short backup time Data recovery must be carried out in a certain order |
| Differential backup | Back up all files that have changed since the last full backup. Differential backup refers to the period from one full backup to differential backup Backup of files added or modified. When restoring, we only need to restore the first full backup and the last differential backup. |
9.2 mysql backup tool mysqldump
//Syntax:
mysqldump [OPTIONS] database [tables ...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
//Common OPTIONS:
-uUSERNAME //Specify database user name
-hHOST //Specify the server host, please use the ip address
-pPASSWORD //Specifies the password of the database user
-P# //Specify the port on which the database listens. The # port here should be replaced by the actual port number, such as - P3307
mysql> show databases; (Write some databases and tables in the database in advance!)
+--------------------+
| Database |
+--------------------+
| information_schema |
| gj |
| lzz |
| mysql |
| performance_schema |
| sys |
| zzh |
+--------------------+
7 rows in set (0.00 sec)
mysql> use lzz;
Database changed
mysql> show tables;
+---------------+
| Tables_in_lzz |
+---------------+
| lg |
| lp |
+---------------+
2 rows in set (0.00 sec)
(Backup all data!)
[root@lzz ~]# mysqldump -uroot -p123liuZEzheng! --all-databases > all_20210506.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@lzz ~]# ls
all_20210506.sql
(Backup database lzz!)
[root@lzz ~]# mysqldump -uroot -p123liuZEzheng! --databases lzz > lzz_20210506.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@lzz ~]# ls
all_20210506.sql lzz_20210506.sql
(Backup database lzz Table in lp!!)
[root@lzz ~]# mysqldump -uroot -p123liuZEzheng! lzz lp > lp_20210506.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@lzz ~]# ls
all_20210506.sql lp_20210506.sql lzz_20210506.sql9.3 database recovery!
mysql> drop database lzz; (Delete database!)
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gj |
| mysql |
| performance_schema |
| sys |
| zzh |
+--------------------+
6 rows in set (0.00 sec)
(Restore database!!!)
[root@lzz ~]# mysql -uroot -p123liuZEzheng! < lzz_20210506.sql mysql
[Warning] Using a password on the command line interface can be insecure.
[root@lzz ~]# mysql -uroot -p123liuZEzheng! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| gj |
| lzz |
| mysql |
| performance_schema |
| sys |
| zzh |
+--------------------+
Database changed
mysql> show tables;
+---------------+
| Tables_in_lzz |
+---------------+
| lg |
| lp |
+---------------+
2 rows in set (0.00 sec)
mysql> drop table lp; (Delete tables in the database!)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_lzz |
+---------------+
| lg |
+---------------+
1 row in set (0.00 sec)
mysql> source lp_20210506.sql; (Reply to table in database!!)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_lzz |
+---------------+
| lg |
| lp |
+---------------+
2 rows in set (0.00 sec)
mysql> drop database gj; (Delete multiple databases!!!)
Query OK, 0 rows affected (0.00 sec)
mysql> drop database lzz;
Query OK, 2 rows affected (0.00 sec)
mysql> drop database zzh;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
[root@lzz ~]# mysql -uroot -p123liuZEzheng! < all_ 20210506.sql (restore the entire database!)
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@lzz ~]# mysql -uroot -p123liuZEzheng! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| gj |
| lzz |
| mysql |
| performance_schema |
| sys |
| zzh |
+--------------------+