本文档详细介绍了如何在Linux环境下通过二进制格式安装MySQL，包括下载安装包、创建用户和组、解压软件、修改目录权限、配置环境变量、创建数据存放目录、初始化数据库、设置新密码、免密登录、配置服务启动脚本、启动MySQL服务以及备份和恢复数据库的步骤。此外，还涉及到了MySQL的差异备份和恢复，以及如何处理启动时的依赖问题。
二进制格式mysql安装
//下载二进制格式的mysql软件包
[root@localhost src]# wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]# ls
debug kernels mysql-5.7.31-linux-glibc2.12-x86_64.tar
//创建用户和组
[root@localhost src]# useradd -r -M -s/sbin/nologin mysql
[root@localhost src]# id mysql
uid=997(mysql) gid=995(mysql) groups=995(mysql)
//解压软件至/usr/local/
[root@localhost src]# tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ln -s mysql-5.7.31-linux-glibc2.12-x86_64 mysql //建立软连接
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root 6 Aug 12 2018 bin
drwxr-xr-x. 2 root root 6 Aug 12 2018 etc
drwxr-xr-x. 2 root root 6 Aug 12 2018 games
drwxr-xr-x. 2 root root 6 Aug 12 2018 include
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib64
drwxr-xr-x. 2 root root 6 Aug 12 2018 libexec
lrwxrwxrwx. 1 root root 36 Oct 22 14:40 mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 7161 31415 129 Jun 2 21:11 mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 Aug 12 2018 sbin
drwxr-xr-x. 5 root root 49 Sep 21 12:39 share
drwxr-xr-x. 2 root root 6 Aug 12 2018 src
//修改目录/usr/local/mysql的属主属组
[root@localhost local]# chown -R mysql.mysql mysql*
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root 6 Aug 12 2018 bin
drwxr-xr-x. 2 root root 6 Aug 12 2018 etc
drwxr-xr-x. 2 root root 6 Aug 12 2018 games
drwxr-xr-x. 2 root root 6 Aug 12 2018 include
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib64
drwxr-xr-x. 2 root root 6 Aug 12 2018 libexec
lrwxrwxrwx. 1 mysql mysql 36 Oct 22 14:40 mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 Jun 2 21:11 mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 Aug 12 2018 sbin
drwxr-xr-x. 5 root root 49 Sep 21 12:39 share
drwxr-xr-x. 2 root root 6 Aug 12 2018 src
//添加环境变量
[root@localhost ~]# vi /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
[root@localhost ~]# source /etc/profile.d/mysql.sh
[root@localhost ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@localhost ~]#
//建立数据存放目录
[root@localhost ~]# mkdir /opt/data
[root@localhost ~]# chown -R mysql.mysql /opt/data
[root@localhost ~]# ll /opt/
total 0
drwxr-xr-x. 2 mysql mysql 6 Oct 22 14:46 data
[root@localhost ~]#
//初始化数据库
[root@localhost ~]# mysql -- initialize --user=mysql --datadir=/opt/data/
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
[root@localhost ~]# mysql -- initialize --user=mysql --datadir=/opt/data/
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data/
2020-10-22T10:58:20.366461Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-10-22T10:58:20.836808Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-10-22T10:58:20.877554Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-10-22T10:58:20.936815Z 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: 7f889fc7-1455-11eb-bfed-000c296e476d.
2020-10-22T10:58:20.937893Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-10-22T10:58:21.974574Z 0 [Warning] CA certificate ca.pem is self signed.
2020-10-22T10:58:22.254874Z 1 [Note] A temporary password is generated for root@localhost: <lKf+9YSLqfs
//请注意，这个命令的最后会生成一个临时密码，此处密码<lKf+9YSLqfs
//再次注意，这个密码是随机的，你的不会跟我一样，一定要记住这个密码，因为一会登录时会用到
//生成配置文件
[root@localhost ~]# 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
//配置服务启动脚本
[root@localhost ~]# cd /usr/local/mysql
[root@localhost mysql]# ls
bin include LICENSE README support-files
docs lib man share
[root@localhost mysql]# cd support-files/
[root@localhost support-files]# file mysql.server mysql.server: POSIX shell script, ASCII text executable
[root@localhost support-files]# cp mysql.server /etc/init.d//mysqld
[root@localhost ~]# vi /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/opt/data
//启动mysql
[root@localhost ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
SUCCESS!
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
[root@localhost ~]# ps -ef|grep mysql
root 1509 1 0 19:11 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql 1697 1509 0 19:11 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
root 1767 1300 0 19:15 pts/0 00:00:00 grep --color=auto mysql
[root@localhost ~]# yum -y install ncurses-compat-libs
//使用临时密码登录mysql，然后修改密码
[root@localhost ~]# 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.31
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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>
//设置新密码
mysql> set password = password('1234');
Query OK, 0 rows affected, 1 warning (0.00 sec)
//免密登录
[root@localhost ~]# vi .my.cnf
[client]
user = root
password =1234
~
mysql配置文件
mysql的配置文件为/etc/my.cnf
配置文件查找次序：如果存在多个配置文件且多个配置文件中均有设定，则最后找到的配置文件生效
/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/
mysql常用配置文件参数：
| 参数 | 说明 |
|---|---|
| port = 3306 | 设置监听端口 |
| socket = /tmp/mysql.sock | 指定套接字文件位置 |
| basedir = /usr/local/mysql | 指定MySQL的安装路径 |
| datadir = /data/mysql | 指定MySQL的数据存放路径 |
| pid-file = /data/mysql/mysql.pid | 指定进程ID文件存放路径 |
| user = mysql | 指定MySQL以什么用户的身份提供服务 |
| skip-name-resolve | 禁止MySQL对外部连接进行DNS解析使用这一选项可以消除MySQL进行DNS解析的时间。若开启该选项，则所有远程主机连接授权都要使用IP地址方式否则MySQL将无法正常处理连接请求 |
MySQL备份工具
//mysqldump语法
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
//常用OPTIONS
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口
MariaDB [yt]> show tables;
+--------------+
| Tables_in_yt |
+--------------+
| student |
| student1 |
+--------------+
2 rows in set (0.000 sec)
//备份
[root@localhost ~]# mysqldump -uroot -p1234 yt student >student.sql
[root@localhost ~]# ls
anaconda-ks.cfg student.sql
[root@localhost ~]#
//恢复
MariaDB [yt]> source student.sql
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
MariaDB [yt]> show tables;
+--------------+
| Tables_in_yt |
+--------------+
| student |
| student1 |
+--------------+
2 rows in set (0.000 sec)
//备份多张表
[root@localhost ~]# mysqldump -uroot -p1234 yt student student1 >student202010231555.sql
[root@localhost ~]# ls
anaconda-ks.cfg student.sql
student202010231555.sql
//备份多个数据库
[root@localhost ~]# mysqldump --databases runtime lili > multi_database-202010231604.sql
[root@localhost ~]# ls
anaconda-ks.cfg
multi_database-202010231604.sql
在外面恢复
[root@localhost ~]# mysql yt < student202010231555.sql
[root@localhost ~]# mysql -e 'show tables from yt;'
+--------------+
| Tables_in_yt |
+--------------+
| student |
| student1 |
+--------------+
[root@localhost ~]#
差异备份与恢复
mysql差异备份
开启MySQL服务器的二进制日志功能
[root@localhost ~]# vi /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /opt/data/mysql.pid
skip-name-resolve
server-id=10
log-bin=mysql_bi
对数据库进行完全备份
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yt |
| yx |
| yy |
+--------------------+
7 rows in set (0.00 sec)
MariaDB [(none)]> show tables from yy;
+--------------+
| Tables_in_yy |
+--------------+
| basic_info |
| info |
+--------------+
2 rows in set (0.00 sec)
MariaDB [(none)]>
//完全备份
[root@localhost ~]# mysqldump -uroot -p123123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202010211406.sql
[root@localhost ~]# ls
all-201902211406.sql anaconda-ks.cfg
[root@localhost ~]#
//增加新内容
mysql> insert student (name,age) values ('lili',25),('xiaozhang',26);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
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 | chenshou | 10 |
| 9 | wangqu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | lili | 25 |
| 13 | xiaozhang | 26 |
+----+-------------+------+
13 rows in set (0.00 sec)
MariaDB [yt]> update student set age = 15 where name = 'tom';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql差异备份恢复
模拟误删数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| yt |
+--------------------+
5 rows in set (0.00 sec)
//删除
[root@localhost ~]# mysql -uroot -p1234 -e 'drop database yt;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]#
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
刷新创建新的二进制日志
[root@localhost ~]# ll /opt/data/
[root@localhost ~]# ll /opt/data/
total 122972
-rw-r-----. 1 mysql mysql 56 Oct 26 19:16 auto.cnf
-rw-------. 1 mysql mysql 1680 Oct 26 19:16 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Oct 26 19:16 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Oct 26 19:16 client-cert.pem
-rw-------. 1 mysql mysql 1680 Oct 26 19:16 client-key.pem
-rw-r-----. 1 mysql mysql 353 Oct 26 19:31 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Oct 26 20:21 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Oct 26 20:21 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Oct 26 19:16 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Oct 26 20:05 ibtmp1
-rw-r-----. 1 mysql mysql 18024 Oct 26 20:19 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 Oct 26 19:16 mysql
-rw-r-----. 1 mysql mysql 5 Oct 26 20:05 mysql.pid
drwxr-x---. 2 mysql mysql 8192 Oct 26 19:16 performance_schema
-rw-------. 1 mysql mysql 1676 Oct 26 19:16 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Oct 26 19:16 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Oct 26 19:16 server-cert.pem
-rw-------. 1 mysql mysql 1680 Oct 26 19:16 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Oct 26 19:16 sys
//刷新创建新的二进制日志
[root@localhost ~]# mysqladmin -uroot -p1234 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data/
total 122972
-rw-r-----. 1 mysql mysql 56 Oct 26 19:16 auto.cnf
-rw-------. 1 mysql mysql 1680 Oct 26 19:16 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Oct 26 19:16 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Oct 26 19:16 client-cert.pem
-rw-------. 1 mysql mysql 1680 Oct 26 19:16 client-key.pem
-rw-r-----. 1 mysql mysql 353 Oct 26 19:31 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Oct 26 20:24 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Oct 26 20:24 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Oct 26 19:16 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Oct 26 20:05 ibtmp1
-rw-r-----. 1 mysql mysql 18024 Oct 26 20:19 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 Oct 26 19:16 mysql
-rw-r-----. 1 mysql mysql 5 Oct 26 20:05 mysql.pid
drwxr-x---. 2 mysql mysql 8192 Oct 26 19:16 performance_schema
-rw-------. 1 mysql mysql 1676 Oct 26 19:16 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Oct 26 19:16 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Oct 26 19:16 server-cert.pem
-rw-------. 1 mysql mysql 1680 Oct 26 19:16 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Oct 26 19:16 sys
恢复完全备份
[root@localhost ~]# mysql -uroot -p1234 <all-202010211406.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p1234 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| yt |
+--------------------+
[root@localhost ~]#
恢复差异备份
[root@localhost ~]# ll /opt/data/
total 110688
-rw-r-----. 1 mysql mysql 56 Oct 26 15:58 auto.cnf
-rw-------. 1 mysql mysql 1676 Oct 26 15:58 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Oct 26 15:58 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Oct 26 15:58 client-cert.pem
-rw-------. 1 mysql mysql 1680 Oct 26 15:58 client-key.pem
-rw-r-----. 1 mysql mysql 185 Oct 26 17:01 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Oct 26 17:01 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Oct 26 17:01 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Oct 26 15:58 ib_logfile1
-rw-r-----. 1 mysql mysql 40175 Oct 26 17:01 localhost.localdomain.err
-rw-r-----. 1 mysql mysql 177 Oct 26 16:58 mysql_bi.000001
-rw-r-----. 1 mysql mysql 177 Oct 26 17:01 mysql_bi.000002
-rw-r-----. 1 mysql mysql 177 Oct 26 17:01 mysql_bi.000003
-rw-r-----. 1 mysql mysql 54 Oct 26 17:01 mysql_bi.index
-rw-------. 1 mysql mysql 1676 Oct 26 15:58 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Oct 26 15:58 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Oct 26 15:58 server-cert.pem
-rw-------. 1 mysql mysql 1680 Oct 26 15:58 server-key.pem
mysql> show binlog events in 'mysql_bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 | 4 | Format_desc | 10 | 123 | Server ver: 5.7.31-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids | 10 | 154 | |
| mysql_bin.000002 | 154 | Anonymous_Gtid | 10 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 219 | Query | 10 | 294 | BEGIN |
| mysql_bin.000002 | 294 | Table_map | 10 | 352 | table_id: 124 (maqiang.student) |
| mysql_bin.000002 | 352 | Write_rows | 10 | 420 | table_id: 124 flags: STMT_END_F |
| mysql_bin.000002 | 420 | Xid | 10 | 451 | COMMIT /* xid=992 */ |
| mysql_bin.000002 | 451 | Anonymous_Gtid | 10 | 516 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 516 | Query | 10 | 591 | BEGIN |
| mysql_bin.000002 | 591 | Table_map | 10 | 649 | table_id: 124 (maqiang.student) |
| mysql_bin.000002 | 649 | Update_rows | 10 | 715 | table_id: 124 flags: STMT_END_F |
| mysql_bin.000002 | 715 | Xid | 10 | 746 | COMMIT /* xid=995 */ |
| mysql_bin.000002 | 746 | Anonymous_Gtid | 10 | 811 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 811 | Query | 10 | 912 | drop database maqiang |
| mysql_bin.000002 | 912 | Rotate | 10 | 959 | mysql_bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
使用mysqlbinlog恢复差异备份(此时恢复到删除之前的811,在mysql_bin.000002文件里)
[root@localhost ~]# mysqlbinlog --stop-position=811 /opt/data/mysql_bin.000002 |mysql -uroot -pmaqiang123
mysql: [Warning] Using a password on the command line interface can be insecure
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| linux20200727 |
| maqiang |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
//使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysql -e 'select * from maqiang.student;'
+----+---------+-------+
| id | name | score |
+----+---------+-------+
| 1 | tom | 20 |
| 2 | jerry | 98 |
| 3 | zs | 60 |
| 4 | lisi | 90 |
| 5 | wnagwu | 87 |
| 6 | qianliu | 67 |
+----+---------+-------+**
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
