mysql部署及远程访问权限开启

菜鸟安装教程

mysql离线安装

参考链接
下载tar包安装以下文件：
Centos7 5.7.21版本下载地址

rpm -ivh mysql-community-common-8.0.15-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.15-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.15-1.el7.x86_64.rpm
rpm -ivh mysql-community-embedded-compat-8.0.15-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-8.0.15-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.15-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.15-1.el7.x86_64.rpm

chown -R mysql:mysql /var/lib/mysql/
mysqld --initialize
systemctl start mysqld

其中有几步需要改变一下：
1、chown -R mysql /var/lib/mysql
2、ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'awifi@123';，这是登陆之后的修改，登陆初始密码去/var/log/mysqld.log查看，其中启动的报错也去这里看
3、修改启动端口这类去/etc/my.inf添加port参数即可
1，登进MySQL之后，

2，输入以下语句，进入mysql库：

use mysql
3，更新域属性，'%'表示允许外部访问：

update user set host='%' where user ='root';

4，执行以上语句之后再执行：

FLUSH PRIVILEGES;
5，再执行授权语句：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;

然后外部就可以通过账户密码访问了。
centos7:
mysql5.7 rpm地址：sudo wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
centos6:
mysql5.7 rpm地址：wget http://repo.mysql.com/yum/mysql-5.7-community/el/6/x86_64/mysql57-community-release-el6-7.noarch.rpm
其他的可参考菜鸟教程，如果已安装需要卸载mysql，切记用service mysql stop
先停掉服务再卸载
遇到的问题：Public key for mysql-community-libs-compat-xxx.rpm is not installed
解决方案：rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

针对表创建特殊用户

drop database if exists litemall;
drop user if exists 'litemall'@'%';
-- 支持emoji：需要mysql数据库参数： character_set_server=utf8mb4
create database litemall default character set utf8mb4 collate utf8mb4_unicode_ci;
use litemall;
create user 'litemall'@'%' identified by 'litemall123456';
grant all privileges on litemall.* to 'litemall'@'%';
flush privileges;

如果yum update遇到报错：Error: Package tuple ('mysql-libs', 'x86_64', '0', '5.1.73', '7.el6') could not be found in rpmdb，使用yum clean all再yum update
mysql卸载
参考链接
mysql5.7yum安装

批量修改表名和列名为小写

#修改表名
select concat('rename table ', table_name, ' to ' , lower(table_name) , ';')from information_schema.tables where table_schema = 'your_schema_name';
#修改列名
SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE `', COLUMN_NAME, '` `',
LOWER(COLUMN_NAME), '` ', COLUMN_TYPE, ';')
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'litemall'

忘记mysql密码

在/etc/my.cnf里加入skip-grant-tables

重启mysql服务，可以直接进入mysql，执行：

update mysql.user set password=password('123456') where host='localhost' and user='root';
#如果出现Unknown column 'password' in 'field list'的报错，执行下面语句
update mysql.user set authentication_string=password('g33&WDvjV4xmVR$Vb0') where user='root'
flush privileges;

重新注释上一步的代码，重启mysql即可
参考文档

修改账号密码

alter user 'root'@'%' IDENTIFIED BY 'password';
#如果上面的不行试试下面的
alter user 'root'@'localhost' IDENTIFIED BY 'password';

清空外键关联的表

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;

批量删除或清空表

其中table_schema代表数据库，如果涉及多个库，可以在下列table_schema in (‘awifi_alf’,‘db2’)

select CONCAT('truncate table ',table_schema,'.',TABLE_NAME, ';') from INFORMATION_SCHEMA.TABLES where table_schema in ('awifi_alf') AND table_name LIKE 'wii_user%';

+------------------------------------------------------------+
| CONCAT('truncate TABLE ',table_schema,'.',TABLE_NAME, ';') |
+------------------------------------------------------------+
| truncate TABLE dbname.ABOUTUSINFO;                           |
| truncate TABLE dbname.ABUTMENT;                              |
| truncate TABLE dbname.ABUTMENTFILE;                         |
| truncate TABLE dbname.ACHVORG;                               |
| truncate TABLE dbname.WORKFLOWNODE;                         |
| truncate TABLE dbname.ZONESERVICE;                           |
| truncate TABLE dbname.ZONESERVICEFILE;                      |
+------------------------------------------------------------+

把上述命令复制到文本编辑器里将|替换为空格即可