二进制格式mysql
1.二进制MySQL安装
#下载二进制格式的mysql软件包 wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz #安装依赖包 [root@localhost ~] yum -y install ncurses-devel openssl-devel openssl cmake mariadb-devel ncurses-compat-libs #创建用户和组 [root@localhost ~]# useradd -r -M -s /sbin/nologin mysql #解压软件包至/usr/local/ [root@localhost ~]# ls anaconda-ks.cfg mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz [root@localhost ~]# tar -xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ [root@localhost ~]# cd /usr/local/ [root@localhost local]# ls apr bin games include lib64 mysql-5.7.31-linux-glibc2.12-x86_64 sbin src apr-util etc httpd lib libexec pcre share [root@localhost local]# ln -sv mysql-5.7.31-linux-glibc2.12-x86_64/ mysql 'mysql' -> 'mysql-5.7.31-linux-glibc2.12-x86_64/' #修改目录/usr/local/mysql的所有者和所属组 [root@localhost local]# chown -R mysql.mysql mysql [root@localhost local]# ll -d mysql lrwxrwxrwx. 1 mysql mysql 36 Dec 28 18:31 mysql -> mysql-5.7.31-linux-glibc2.12-x86_64/ #添加环境变量，头文件，lib库文件，man帮助等。 #添加环境变量 [root@localhost ~]# vim /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/httpd/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin #添加头文件 [root@localhost ~]# ln -sv /usr/local/mysql/include/ /usr/include/mysql '/usr/include/mysql' -> '/usr/local/mysql/include/' #lib库文件 [root@localhost mysql]# vim /etc/ld.so.conf.d/mysql.conf /usr/local/mysql/lib [root@localhost mysql]# ldconfig #添加man文件 [root@localhost mysql]# vim /etc/man_db.conf ......................... #MANDATORY_MANPATH /usr/src/pvm3/man # MANDATORY_MANPATH /usr/man MANDATORY_MANPATH /usr/share/man MANDATORY_MANPATH /usr/local/share/man MANDATORY_MANPATH /usr/local/httpd/man MANDATORY_MANPATH /usr/local/mysql/man ............................... #建立存放数据的目录 [root@localhost ~]# mkdir /opt/data [root@localhost mysql]# chown -R mysql.mysql /opt/data/ [root@localhost mysql]# ll -d /opt/data/ drwxr-xr-x. 5 mysql mysql 4096 Dec 28 18:44 /opt/data/ #初始化数据库 --initialize-insecure 不会产生随机密码，第一次登陆数据库使用空密码 [root@localhost mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/ 2020-12-28T10:44:09.962318Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-12-28T10:44:10.182068Z 0 [Warning] InnoDB: New log files created, LSN=45790 2020-12-28T10:44:10.225557Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2020-12-28T10:44:10.305322Z 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: 9e31ced2-48f9-11eb-b49d-000c29613fc2. 2020-12-28T10:44:10.306143Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2020-12-28T10:44:10.792012Z 0 [Warning] CA certificate ca.pem is self signed. 2020-12-28T10:44:10.904959Z 1 [Note] A temporary password is generated for root@localhost: 0YE%ilPBm#2O 这个命令的最后会生成一个随机的临时密码，记住此密码：0YE%ilPBm#2O
2.MySQL配置文件
mysql的配置文件为/etc/my.cnf
配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效
/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf
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将无法正常处理连接请求 |
|
skip-grand-tables |
当忘记mysql用户密码的时候，可以在mysql配置文件中配置该参数，跳过权限表验证，不需要密码即可登录mysql |
生成配置文件
[root@localhost ~]# 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@localhost ~]# ls /usr/local/mysql/support-files/ magic mysqld_multi.server mysql-log-rotate mysql.server [root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [root@localhost ~]# ll /etc/init.d/mysqld //确保有执行权限 -rwxr-xr-x. 1 root root 10576 Dec 28 22:18 /etc/init.d/mysqld [root@localhost ~]# vim /etc/init.d/mysqld # If you change base dir, you must also change datadir. These may get # overwritten by settings in the MySQL configuration files. 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 128 [::]:22 [::]:* LISTEN 0 80 *:3306 *:* LISTEN 0 128 *:80
修改密码
[root@localhost ~]# mysql -uroot -p0YE%ilPBm#2O
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 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> set password = password('diqiyao123!')
-> ;
Query OK, 0 rows affected, 1 warning (0.00 sec
[root@localhost ~]# mysql -uroot -p0YE%ilPBm#2O 报错 mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory [root@localhost ~]# yum -y install ncurses-compat-libs 解决方法
密码忘记破解密码
#修改配置文件
[root@localhost ~]# vim /etc/my.cnf
........
skip-grant-tables #添加跳过授权表
#重启访问
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
#登录mysql修改密码
root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
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> 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> select * user /G;
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 'user /G' at line 1
mysql> select * from user\G;
*************************** 1. row ***************************
Host: localhost
User: root
..........................................
..........................................
authentication_string: *B1E7B30F66CD26E2A1DB68B6292C274FB7EB3875
password_expired: N
password_last_changed: 2020-12-28 22:36:23
password_lifetime: NULL
account_locked: N
mysql> update user set authentication_string=password(123456) where User='root' and Host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> exit
Bye
#修改配置文件删除skip-grant-tables
#重启服务
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
#验证登录
[root@localhost ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost ~]# 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 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.
