linux下mysql 5.7.22 安装
二进制安装
1.下载https://dev.mysql.com/downloads/mysql/5.6.html#downloads
2.官方文档https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html
[root@localhost local]# yum install libaio Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirrors.aliyun.com * extras: mirrors.huaweicloud.com * updates: mirrors.aliyun.com Package libaio-0.3.109-13.el7.x86_64 already installed and latest version Nothing to do [root@localhost local]# pwd /usr/local [root@localhost local]# tar -zxvf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz [root@localhost local]# ln -s mysql-5.7.22-linux-glibc2.12-x86_64 mysql [root@localhost local]groupadd mysql [root@localhost local] useradd -r -g mysql -s /bin/false mysql [root@localhost local] chown -R mysql:mysql mysql [root@localhost local]# cd mysql [root@localhost mysql]# ll total 40 drwxr-xr-x. 2 root root 4096 May 15 03:59 bin -rw-r--r--. 1 7161 31415 17987 Mar 4 21:40 COPYING drwxr-x---. 5 mysql mysql 4096 May 15 04:39 data drwxr-xr-x. 2 root root 55 May 15 03:59 docs drwxr-xr-x. 3 root root 4096 May 15 03:58 include drwxr-xr-x. 5 root root 229 May 15 03:59 lib drwxr-xr-x. 4 root root 30 May 15 03:59 man -rw-r--r--. 1 7161 31415 2478 Mar 4 21:40 README drwxr-xr-x. 28 root root 4096 May 15 03:59 share drwxr-xr-x. 2 root root 90 May 15 03:59 support-files [root@localhost mysql]bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
[root@localhost mysql]# bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data [root@localhost mysql]# cd support-files/ [root@localhost support-files]# vi /etc/my.cnf [root@localhost mysql]# cat /etc/my.cnf [mysqld] basedir=/usr/local/mysql/ datadir=/usr/local/mysql/data/
character_set_server = utf8 [root@localhost support-files]# cd .. [root@localhost mysql]# cd bin/ [root@localhost bin]# ./mysqld_safe --user=mysql & [1] 2942
启动完毕
登录，修改root密码 设置远程登录
[root@localhost mysql]# 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.22
Copyright (c) 2000, 2018, 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("root");
Query OK, 0 rows affected, 1 warning (0.00 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 host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY '......' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
设置开机自启
[root@localhost support-files]# cp mysql.server /etc/init.d/mysql [root@localhost support-files]# chmod +x /etc/init.d/mysql [root@localhost support-files]# chkconfig --add mysql [root@localhost support-files]# chkconfig --list mysql
[root@localhost support-files]# service mysql stop
[root@localhost support-files]# service mysql start
配置环境变量：
[root@localhost local]# mysql -uroot -p -bash: mysql: command not found [root@localhost local]# ln -s /usr/local/mysql/bin/mysql /usr/bin [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.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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> 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 host = '%' where user = 'root'; Query OK, 0 rows affected (0.11 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> select host,user from user; +-----------+---------------+ | host | user | +-----------+---------------+ | % | myuser | | % | root | | % | sec_user | | localhost | mysql.session | | localhost | mysql.sys | | localhost | sec_user | +-----------+---------------+ 6 rows in set (0.00 sec)
C:\Users\Administrator>mysql -u sec_user -h 192.168.222.13 -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.7.22 MySQL Community Server (GPL) Copyright (c) 2000, 2015, 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 | | securities_master | +--------------------+ 2 rows in set (0.00 sec) mysql> use securities_master; Database changed mysql> show tables; +-----------------------------+ | Tables_in_securities_master | +-----------------------------+ | daily_price | | data_vendor | | exchange | | symbol | +-----------------------------+ 4 rows in set (0.00 sec) mysql>
注意：1.mysql默认的字符集是Latin ，需要修改my.cnf 设置为utf-8，
[client] default-character-set=utf8 [mysql] default-character-set=utf8 [mysqld] init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8' character-set-server=utf8 collation-server=utf8_unicode_ci skip-character-set-client-handshake
并且重启，验证
mysql> show variables like 'character_set_database'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | character_set_database | utf8 | +------------------------+-------+ 1 row in set (0.01 sec) mysql> show variables like 'character%'; +--------------------------+----------------------------------------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.7.22-linux-glibc2.12-x86_64/share/charsets/ | +--------------------------+----------------------------------------------------------------+
2.开机启动关闭防火墙
Centos7的防火墙用firewalld.service，而不是iptable……
systemctl stop firewalld.service
systemctl disable firewalld.service
3.mysql创建用户
1）MySQL创建新用户以及ERROR 1396 (HY000)问题解决
Assume the user is there, so drop the user
After deleting the user, there is need to flush the mysql privileges
Now create the user.
flush privileges;（刷新系统权限表）
每次建完用户后执行flush privileges命令
删除用户 drop user XX;
4.忘记密码进行更改
1）、关闭mysql
service mysqld stop //linux下使用
net stop mysql //window下使用
2）、屏蔽权限
mysqld_safe --skip-grant-table //linux下使用
mysqld --skip-grant-table //window下使用
3.）新开一个窗口进行密码修改
4.存在空密码新安装的
1）select host,user,password from user;查看是否空密码
2）.关闭mysql
# service mysqld stop
3）.屏蔽权限
# mysqld_safe --skip-grant-table
4）新开起一个终端输入
# mysql -u root mysql
mysql> delete from user where USER=''; //删除空密码
mysql> FLUSH PRIVILEGES;
mysql> \q
参考文档：
https://blog.csdn.net/qq_37995908/article/details/79092363
https://blog.csdn.net/ctypyb2002/article/details/79757070
https://blog.csdn.net/mengfei86/article/details/51189624
We only live once, and time just goes by.
