Mysql二进制安装与备份的全过程记录

[root@localhost mysql]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
–2021-05-04 20:34:21– https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
Resolving downloads.mysql.com (downloads.mysql.com)… 137.254.60.14
Connecting to downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:443… connected.
HTTP request sent, awaiting response… 302 Found
Location: https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz [following]
–2021-05-04 20:34:23– https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
Resolving cdn.mysql.com (cdn.mysql.com)… 223.119.236.209
Connecting to cdn.mysql.com (cdn.mysql.com)|223.119.236.209|:443… connected.
HTTP request sent, awaiting 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- 100%[================>] 631.06M 11.3MB/s in 57s
2021-05-04 20:35:21 (11.0 MB/s) – ‘mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz’ saved [661718255/661718255]

[root@localhost mysql]# useradd -r -M -s /sbin/nologin mysql

[root@localhost mysql]# tar xf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /usr/local

[root@localhost local]# mv mysql-5.7.33-linux-glibc2.12-x86_64/ mysql
[root@localhost local]# ls
apache apr-util etc include lib64 mysql share
apr bin games lib libexec sbin src

[root@localhost local]# chown -R mysql.mysql /usr/local/mysql/
[root@localhost local]# ll
drwxr-xr-x. 9 mysql mysql 129 五月 4 20:40 mysql

[root@localhost mysql]# vim /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
[root@localhost mysql]# . /etc/profile.d/mysql.sh
[root@localhost mysql]# which mysql
/usr/local/mysql/bin/mysql

[root@localhost mysql]# mkdir /opt/mysql_data
[root@localhost mysql]# chown -R mysql.mysql /opt/mysql_data/
[root@localhost mysql]# ll /opt
drwxr-xr-x. 2 mysql mysql 6 五月 4 20:58 mysql_data

[root@localhost mysql]# /usr/local/mysql/bin/mysqld –initialize –user=mysql –datadir=/opt/mysql_data/ //初始化 控制mysql的用户 数据存放目录
2021-05-04T13:01:07.403961Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-04T13:01:07.683107Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-04T13:01:07.739366Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-04T13:01:07.746720Z 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: caa21b8a-acd8-11eb-b7ab-000c294bb269.
2021-05-04T13:01:07.747895Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2021-05-04T13:01:09.096727Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-04T13:01:09.485357Z 1 [Note] A temporary password is generated for root@localhost: q_UG8?3sa/l%
[root@localhost mysql]# vim password
q_UG8?3sa/l%

[root@localhost mysql]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql //程序位置
datadir = /opt/mysql_data //数据存放位置
socket = /tmp/mysql.sock //文件套接字位置
port = 3306 //端口
pid-file = /opt/mysql_data/mysql.pid //进程文件位置
user = mysql //用户
skip-name-resolve //跳过域名解析，即直接在内网使用ip连接数据库

[root@localhost ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# sed -ri ‘s#^(basedir=).*#\1/usr/local/mysql#g’ /etc/init.d/mysqld
[root@localhost ~]# sed -ri ‘s#^(datadir=).*#\1/opt/mysql_data#g’ /etc/init.d/mysqld
[root@localhost mysql]# head -47 /etc/init.d/mysqld |tail -2
basedir=/usr/local/mysql
datadir=/opt/mysql_data
[root@localhost mysql]# service mysqld start
Starting MySQL.Logging to ‘/opt/data/localhost.localdomain.err’.
SUCCESS!
[root@localhost mysql]# chkconfig mysqld on
[root@localhost mysql]# chkconfig –list
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.
If you want to list systemd services use ‘systemctl list-unit-files’.
To see services enabled on particular target use
‘systemctl list-dependencies [target]’.
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@localhost mysql]# ss -anlt
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 *:443 *:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 *:80 *:*
LISTEN 0 128 [::]:22 [::]:*

[root@localhost mysql]# ln -s /usr/local/mysql/include /usr/include/mysql [root@localhost mysql]# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[root@localhost mysql]# ldconfig //重新读取配置文件

[root@localhost local]# mysql -uroot -p
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory //缺少包
[root@localhost local]# dnf provides libncurses.so.5 //查看包所依赖的类库
Warning: failed loading ‘/etc/yum.repos.d/mssql-server.repo’, skipping.
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
Repo : baseos
Matched from:
Provide : libncurses.so.5
[root@localhost local]# dnf -y install ncurses-compat-libs
[root@localhost local]# cat password
/sdjtceDy7F7
[root@localhost local]# 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>

/etc/my.cnf –> /etc/mysql/my.cnf –> –default-extra-file=/PATH/TO/CONF_FILE –> ~/.my.cnf

     mysqldump [OPTIONS] database [tables …]                                                //备份数据表
    mysqldump [OPTIONS] –all-databases [OPTIONS]                                    //备份全部数据库
    mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3…]            //备份某个数据库
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替

[root@localhost ~]# mysqldump -uroot -p –all-databases > /mysql_dump/all_20210504.sql
Enter password:
[root@localhost ~]# ls /mysql_dump/
all_20210504.sql

[root@localhost ~]# mysqldump -uroot -p –databases hanao > /mysql_dump/hanao_db_20210504.sql
Enter password:
[root@localhost ~]# ls /mysql_dump/
all_20210504.sql hanao_db_20210504.sql

[root@localhost ~]# mysqldump -uroot -p hanao student > /mysql_dump/student_table_20210504.sql
Enter password:
[root@localhost ~]# ls /mysql_dump/
all_20210504.sql hanao_db_20210504.sql student_table_20210504.sql

mysql> use hanao
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

[root@localhost ~]# cd /mysql_dump/
[root@localhost mysql_dump]# mysql -uroot -pHa153624.
mysql> use hanao;
Database changed
mysql> source student_table_20210504.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+—————–+
| Tables_in_hanao |
+—————–+
| student |
+—————–+
1 row in set (0.00 sec)

mysql> drop database hanao;
Query OK, 1 row affected (0.00 sec)

[root@localhost mysql_dump]# mysql -uroot -pHa153624. < hanao_db_20210504.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@localhost mysql_dump]# mysql -uroot -pHa153624. -e ‘show databases;’
mysql: [Warning] Using a password on the command line interface can be insecure.
+——————–+
| Database |
+——————–+
| information_schema |
| ha |
| hanao |
| mysql |
| performance_schema |
| sys |
+——————–+

mysql> drop database ha;
Query OK, 1 row affected (0.01 sec)

[root@localhost mysql_dump]# mysql -uroot -pHa153624. < all_20210504.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql_dump]# mysql -uroot -pHa153624. -e ‘show databases;’
mysql: [Warning] Using a password on the command line interface can be insecure.
+——————–+
| Database |
+——————–+
| information_schema |
| ha |
| hanao |
| mysql |
| performance_schema |
| sys |
+——————–+

root@localhost mysql_data]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysql_data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql_data/mysql.pid
user = mysql
skip-name-resolve
server-id=1 //设置服务器标识符
log-bin=mysql_bin //开启二进制日志功能

[root@localhost mysql_data]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

[root@localhost /]# mysql -uroot -pHa153624…
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.33-log MySQL Community Server (GPL)

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> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| hanao |
| mysql |
| performance_schema |
| sys |
+——————–+
5 rows in set (0.00 sec)

mysql> use hanao;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+—————–+
| Tables_in_hanao |
+—————–+
| student |
+—————–+
1 row in set (0.00 sec)

mysql> desc student;
+——-+————–+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+————–+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+——-+————–+——+—–+———+—————-+
3 rows in set (0.00 sec)

mysql> select * from student;
+—-+————-+——+
| id | name | age |
+—-+————-+——+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chengshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+—-+————-+——+
11 rows in set (0.00 sec)

[root@localhost mysql_dump]# mysqldump -uroot -pHa153624… –single-transaction –flush-logs –master-data=2 –all-databases –delete-master-logs > all-20210506.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql_dump]# ll
total 1720
-rw-r–r–. 1 root root 874762 五月 4 22:48 all_20210504.sql
-rw-r–r–. 1 root root 873608 五月 6 18:19 all-20210506.sql
-rw-r–r–. 1 root root 2250 五月 4 22:51 hanao_db_20210504.sql
-rw-r–r–. 1 root root 2108 五月 4 22:52 student_table_20210504.sql

mysql> use hanao;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into student(name,age) value(‘hanao’,22);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student(name,age) value(‘hanao’,21);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student(name,age) value(‘tengjia’,21);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+—-+————-+——+
| id | name | age |
+—-+————-+——+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chengshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | hanao | 22 |
| 13 | hanao | 21 |
| 14 | tengjia | 21 |
+—-+————-+——+
14 rows in set (0.00 sec)

mysql> drop database hanao;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
+——————–+
4 rows in set (0.00 sec)

[root@localhost mysql_dump]# ll /opt/mysql_data/
total 123020
-rw-r—–. 1 mysql mysql 56 五月 4 22:01 auto.cnf
-rw——-. 1 mysql mysql 1680 五月 4 22:01 ca-key.pem
-rw-r–r–. 1 mysql mysql 1112 五月 4 22:01 ca.pem
-rw-r–r–. 1 mysql mysql 1112 五月 4 22:01 client-cert.pem
-rw——-. 1 mysql mysql 1680 五月 4 22:01 client-key.pem
-rw-r—–. 1 mysql mysql 562 五月 6 18:06 ib_buffer_pool
-rw-r—–. 1 mysql mysql 12582912 五月 6 18:24 ibdata1
-rw-r—–. 1 mysql mysql 50331648 五月 6 18:24 ib_logfile0
-rw-r—–. 1 mysql mysql 50331648 五月 4 22:01 ib_logfile1
-rw-r—–. 1 mysql mysql 12582912 五月 6 18:19 ibtmp1
-rw-r—–. 1 mysql mysql 58417 五月 6 18:06 localhost.localdomain.err
drwxr-x—. 2 mysql mysql 4096 五月 4 23:03 mysql
-rw-r—–. 1 mysql mysql 1129 五月 6 18:23 mysql_bin.000010
-rw-r—–. 1 mysql mysql 19 五月 6 18:19 mysql_bin.index
-rw-r—–. 1 mysql mysql 6 五月 6 18:06 mysql.pid
drwxr-x—. 2 mysql mysql 8192 五月 4 22:01 performance_schema
-rw——-. 1 mysql mysql 1680 五月 4 22:01 private_key.pem
-rw-r–r–. 1 mysql mysql 452 五月 4 22:01 public_key.pem
-rw-r–r–. 1 mysql mysql 1112 五月 4 22:01 server-cert.pem
-rw——-. 1 mysql mysql 1676 五月 4 22:01 server-key.pem
drwxr-x—. 2 mysql mysql 8192 五月 4 22:01 sys
[root@localhost mysql_dump]# mysqladmin -uroot -pHa153624… flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql_dump]# ll /opt/mysql_data/
total 123024
-rw-r—–. 1 mysql mysql 56 五月 4 22:01 auto.cnf
-rw——-. 1 mysql mysql 1680 五月 4 22:01 ca-key.pem
-rw-r–r–. 1 mysql mysql 1112 五月 4 22:01 ca.pem
-rw-r–r–. 1 mysql mysql 1112 五月 4 22:01 client-cert.pem
-rw——-. 1 mysql mysql 1680 五月 4 22:01 client-key.pem
-rw-r—–. 1 mysql mysql 562 五月 6 18:06 ib_buffer_pool
-rw-r—–. 1 mysql mysql 12582912 五月 6 18:24 ibdata1
-rw-r—–. 1 mysql mysql 50331648 五月 6 18:24 ib_logfile0
-rw-r—–. 1 mysql mysql 50331648 五月 4 22:01 ib_logfile1
-rw-r—–. 1 mysql mysql 12582912 五月 6 18:19 ibtmp1
-rw-r—–. 1 mysql mysql 58417 五月 6 18:06 localhost.localdomain.err
drwxr-x—. 2 mysql mysql 4096 五月 4 23:03 mysql
-rw-r—–. 1 mysql mysql 1176 五月 6 18:24 mysql_bin.000010
-rw-r—–. 1 mysql mysql 154 五月 6 18:24 mysql_bin.000011
-rw-r—–. 1 mysql mysql 38 五月 6 18:24 mysql_bin.index
-rw-r—–. 1 mysql mysql 6 五月 6 18:06 mysql.pid
drwxr-x—. 2 mysql mysql 8192 五月 4 22:01 performance_schema
-rw——-. 1 mysql mysql 1680 五月 4 22:01 private_key.pem
-rw-r–r–. 1 mysql mysql 452 五月 4 22:01 public_key.pem
-rw-r–r–. 1 mysql mysql 1112 五月 4 22:01 server-cert.pem
-rw——-. 1 mysql mysql 1676 五月 4 22:01 server-key.pem
drwxr-x—. 2 mysql mysql 8192 五月 4 22:01 sys
[root@localhost mysql_dump]# cat /opt/mysql_data/mysql_bin.index
./mysql_bin.000010
./mysql_bin.000011

[root@localhost mysql_dump]# mysql -uroot -pHa153624… < all-20210506.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql_dump]# mysql -uroot -pHa153624… -e ‘show databases;’
mysql: [Warning] Using a password on the command line interface can be insecure.
+——————–+
| Database |
+——————–+
| information_schema |
| hanao |
| mysql |
| performance_schema |
| sys |
+——————–+
[root@localhost mysql_dump]# mysql -uroot -pHa153624… -e ‘select * from hanao.student;’
mysql: [Warning] Using a password on the command line interface can be insecure.
+—-+————-+——+
| id | name | age |
+—-+————-+——+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chengshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+—-+————-+——+

[root@localhost mysql_dump]# mysql -uroot -pHa153624…
mysql> show binlog events in ‘mysql_bin.000010’;
+——————+——+—————-+———–+————-+—————————————+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+——————+——+—————-+———–+————-+—————————————+
| mysql_bin.000010 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.33-log, Binlog ver: 4 |
| mysql_bin.000010 | 123 | Previous_gtids | 1 | 154 | |
| mysql_bin.000010 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| mysql_bin.000010 | 219 | Query | 1 | 292 | BEGIN |
| mysql_bin.000010 | 292 | Table_map | 1 | 347 | table_id: 133 (hanao.student) |
| mysql_bin.000010 | 347 | Write_rows | 1 | 394 | table_id: 133 flags: STMT_END_F |
| mysql_bin.000010 | 394 | Xid | 1 | 425 | COMMIT /* xid=518 */ |
| mysql_bin.000010 | 425 | Anonymous_Gtid | 1 | 490 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| mysql_bin.000010 | 490 | Query | 1 | 563 | BEGIN |
| mysql_bin.000010 | 563 | Table_map | 1 | 618 | table_id: 133 (hanao.student) |
| mysql_bin.000010 | 618 | Write_rows | 1 | 665 | table_id: 133 flags: STMT_END_F |
| mysql_bin.000010 | 665 | Xid | 1 | 696 | COMMIT /* xid=519 */ |
| mysql_bin.000010 | 696 | Anonymous_Gtid | 1 | 761 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| mysql_bin.000010 | 761 | Query | 1 | 834 | BEGIN |
| mysql_bin.000010 | 834 | Table_map | 1 | 889 | table_id: 133 (hanao.student) |
| mysql_bin.000010 | 889 | Write_rows | 1 | 938 | table_id: 133 flags: STMT_END_F |
| mysql_bin.000010 | 938 | Xid | 1 | 969 | COMMIT /* xid=520 */ |
| mysql_bin.000010 | 969 | Anonymous_Gtid | 1 | 1034 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| mysql_bin.000010 | 1034 | Query | 1 | 1129 | drop database hanao |
| mysql_bin.000010 | 1129 | Rotate | 1 | 1176 | mysql_bin.000011;pos=4 |
+——————+——+—————-+———–+————-+—————————————+
20 rows in set (0.00 sec)

[root@localhost mysql_dump]# mysqlbinlog –stop-position=1034 /opt/mysql_data/mysql_bin.000010 |mysql -uroot -pHa153624…
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql_dump]# mysql -uroot -pHa153624… -e ‘select * from hanao.student;’
mysql: [Warning] Using a password on the command line interface can be insecure.
+—-+————-+——+
| id | name | age |
+—-+————-+——+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chengshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | hanao | 22 |
| 13 | hanao | 21 |
| 14 | tengjia | 21 |
+—-+————-+——+