二进制格式mysql安装

//下载二进制格式的mysql软件包

[root@cst ~]# ls
anaconda-ks.cfg  mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz

//创建系统用户mysql和组

[root@cst ~]# useradd -r -M -s /sbin/nologin mysql
[root@cst ~]# id mysql 
uid=994(mysql) gid=991(mysql) groups=991(mysql)

//解压软件至/usr/local/

[root@cst ~]# tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@cst ~]# ls /usr/local/
bin    include  libexec                              share
etc    lib      mysql-5.7.31-linux-glibc2.12-x86_64  src
games  lib64    sbin

// 为mysql建立软链接,方便使用

[root@cst ~]# ln -s /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64 /usr/local/mysql
[root@cst ~]# ll /usr/local/
total 0
drwxr-xr-x. 2 root root    6 May 11  2019 bin
drwxr-xr-x. 2 root root    6 May 11  2019 etc
drwxr-xr-x. 2 root root    6 May 11  2019 games
drwxr-xr-x. 2 root root    6 May 11  2019 include
drwxr-xr-x. 2 root root    6 May 11  2019 lib
drwxr-xr-x. 2 root root    6 May 11  2019 lib64
drwxr-xr-x. 2 root root    6 May 11  2019 libexec
lrwxrwxrwx. 1 root root   46 Dec 28 18:54 mysql -> /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 9 7161 31415 129 Jun  2  2020 mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root    6 May 11  2019 sbin
drwxr-xr-x. 5 root root   49 Nov 29 14:55 share
drwxr-xr-x. 2 root root    6 May 11  2019 src

//修改目录/usr/local/mysql的属主属组

[root@cst ~]# chown -R mysql.mysql /usr/local/mysql
[root@cst ~]# ll /usr/local/
total 0
drwxr-xr-x. 2 root  root    6 May 11  2019 bin
drwxr-xr-x. 2 root  root    6 May 11  2019 etc
drwxr-xr-x. 2 root  root    6 May 11  2019 games
drwxr-xr-x. 2 root  root    6 May 11  2019 include
drwxr-xr-x. 2 root  root    6 May 11  2019 lib
drwxr-xr-x. 2 root  root    6 May 11  2019 lib64
drwxr-xr-x. 2 root  root    6 May 11  2019 libexec
lrwxrwxrwx. 1 mysql mysql  46 Dec 28 18:54 mysql -> /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 9  7161 31415 129 Jun  2  2020 mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root  root    6 May 11  2019 sbin
drwxr-xr-x. 5 root  root   49 Nov 29 14:55 share
drwxr-xr-x. 2 root  root    6 May 11  2019 src

//添加环境变量

[root@cst ~]# echo ‘export PATH=/usr/local/mysql/bin:$PATH‘ >/etc/profile.d/mysql.sh
[root@cst ~]# which mysql
/usr/bin/which: no mysql in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)     
[root@cst ~]# source /etc/profile.d/mysql.sh 
[root@cst ~]# which mysql
/usr/local/mysql/bin/mysql

// 配置其他文件

[root@cst mysql]# ln -s /usr/local/mysql/include/ /usr/include/
mysql
[root@cst mysql]# vim /etc/man_db.conf 

MANDATORY_MANPATH                       /usr/man
MANDATORY_MANPATH                       /usr/share/man
MANDATORY_MANPATH                       /usr/local/share/man
MANDATORY_MANPATH                       /usr/local/mysql/man

[root@cst mysql]# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[root@cst mysql]# ldconfig 

//建立数据存放目录

[root@cst ~]# mkdir /opt/data
[root@cst ~]# chown -R mysql.mysql /opt/data/
[root@cst ~]# ll /opt/
total 0
drwxr-xr-x. 2 mysql mysql 6 Dec 28 19:48 data

//初始化数据库

[root@cst ~]# mysqld --initialize --user=mysql --datadir=/opt/data
2020-12-28T11:53:43.338979Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-12-28T11:53:43.730969Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-12-28T11:53:43.791201Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-12-28T11:53:43.852778Z 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: 55d29c42-4903-11eb-8abc-000c29414cfb.
2020-12-28T11:53:43.853795Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed‘ cannot be opened.
2020-12-28T11:53:44.650198Z 0 [Warning] CA certificate ca.pem is self signed.
2020-12-28T11:53:44.686014Z 1 [Note] A temporary password is generated for root@localhost: :Ud._4,y&ByL

//Ud._4,y&ByL为随机生成的临时密码，用于登录数据库

[root@cst ~]# mysql -uroot -p‘Ud._4,y&ByL‘
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory

//登录提示缺少依赖关系，查找相关的包并下载

[root@cst ~]# yum provides libncurses.so.5
Failed to set locale, defaulting to C.UTF-8
Last metadata expiration check: 0:02:01 ago on Mon Dec 28 22:56:34 2020.
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses
     ...: compatibility libraries
Repo        : BaseOS
Matched from:
Provide    : libncurses.so.5

[root@cst ~]# yum -y install ncurses-compat-libs

//编写配置文件

[root@cst ~]# vim /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@cst ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@cst ~]# vim /etc/init.d/mysqld

basedir=/usr/local/mysql
datadir=/opt/data

//启动服务
[root@cst ~]# service mysqld start 
Starting MySQL.Logging to ‘/opt/data/cst.err‘.
... SUCCESS! 
[root@cst ~]# 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             [::]:* 

//登录mysql
[root@cst ~]# mysql -uroot -p‘Ud._4,y&ByL‘

mysql配置文件

mysql的配置文件为/etc/my.cnf

配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

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

mysql常用配置文件参数：

破解密码登录

1. 修改my.cnf文件

[root@cst ~]# vim /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
skip-grant-tables  //跳过授权表

2. 重启服务

[root@cst ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

3. 登录数据库并修改密码

[root@cst ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31 MySQL Community Server (GPL)

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.12 sec)

mysql> use mysql;
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> update user set authentication_string=password(‘123456‘)where User=‘root‘ and Host=‘localhost‘;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1

4. 修改配置文件，删除skip-grant-tables

[root@cst ~]# vim /etc/my.cnf

5. 重启服务

[root@cst ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

6. 验证

[root@cst ~]# mysql
ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: NO)
[root@cst ~]# mysql -uroot -p123456
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 3
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数据库备份与恢复

数据库常用备份方案

数据库备份方案：

• 全量备份
• 增量备份
• 差异备份

mysql备份工具mysqldump

//新建一个库school，并新建一个表student，插入数据

mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use school
Database changed
mysql> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint);
Query OK, 0 rows affected (0.04 sec)

mysql> insert student(name,age) values(‘haruki‘,20),(‘akira‘,18,),(‘neneka‘,21);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | haruki |   20 |
|  2 | akira  |   18 |
|  3 | neneka |   21 |
+----+--------+------+
3 rows in set (0.00 sec)

// 全量备份数据库

[root@cst ~]# mysqldump -uroot -p1234 --all-databases >all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

//删除school后恢复

[root@cst ~]# mysql -uroot -p1234 -e ‘drop database school;‘
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@cst ~]# 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                |
+--------------------+

[root@cst ~]# mysql -uroot -p1234 < all.sql   
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@cst ~]# 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 |
| school             |
| sys                |
+--------------------+
恢复成功

mysql进阶