CentOS_7 安装MySql5.7

1. 下载mysql的源 wget http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm

2. 安装yum库 yum localinstall -y mysql57-community-release-el7-7.noarch.rpm

3. 安装MySQL yum install -y mysql-community-server

4. 启动MySQL服务 systemctl start mysqld.service

5. MySQL5.7加强了root用户的安全性，因此在第一次安装后会初始化一个随机密码，以下为查看初始随机密码的方式 grep 'temporary password' /var/log/mysqld.log

登录mysql   mysql -u root -p

SET PASSWORD = PASSWORD('你的密码');
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
flush privileges;

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root123';

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

FLUSH  PRIVILEGES;
然后退出后即可用新密码登录。
远程连接授权：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '你的密码' WITH GRANT OPTION;

systemctl start mysqld.service
systemctl stop mysqld.service
systemctl restart mysqld.service
systemctl enable mysqld.service

systemctl start mariadb.service

# 允许最大连接数
max_connections=1000
支持表情数据库
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

# 备份数据库

#!/bin/sh
localdate=$(date "+%Y-%m-%d")
mysqldump -h127.0.0.1 -p3306 -u用户名 -p密码 --single-transaction --databases 数据库1 数据库2 > /data/backup/bb_${localdate}.sql

--single-transaction 这个参数最好加上，避免锁表

常用语句

更新表的字符集

#查询所有表字符集更新语句
SELECT
    CONCAT(
        'ALTER TABLE ',
        TABLE_NAME,
        ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
    )
FROM
    information_schema.`TABLES`
WHERE
    TABLE_SCHEMA = 'my_table';
#更新字符集
ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

查询出来的数据自定义序列号

比如我想从1001开始序列号，那么起始值就定义为1000

select (@i:=@i+1) AS id,code,name from address,(SELECT @i := 1000) AS i