关于mysql:MySQL57的安装与配置

1. 下载 yum repository

   wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
   

2. 装置 yum repository

   yum -y localinstall mysql57-community-release-el7-11.noarch.rpm
   

3. 在线装置 mysql

   yum -y install mysql-community-server

4. 批改配置

   #cat /etc/my.cnf
   [mysqld]
   user=mysql
   port=3306
   log-error=/var/log/mysqld.log
   pid-file=/var/run/mysqld/mysqld.pid
   
   #数据目录
   basedir=/data/mysql
   datadir=/data/mysql/data
   socket=/data/mysql/lib/mysql.sock
   
   #符号链接
   symbolic-links=0
   
   #设置最大连接数
   max_connections=1000
   
   #skip-grant-tables
   sql_mode =STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
   
   #设置 mysql 默认字符集
   character-set-server=utf8
   collation-server=utf8_general_ci
   
   ## 开启 binlog 日志性能
   server-id=1
   log_bin=my-bin
   binlog_format = row
   
   ## 开启慢查问
   slow_query_log=ON
   slow_query_log_file=/var/lib/mysql/slow.log
   long_query_time=1
   
   #敞开明码复杂度
   #validate_password=off 
   #default_password_lifetime=0
   
   #表名不辨别大小写
   lower_case_table_names=1
   innodb_buffer_pool_instances=4
   
   #长期表大小
   tmp_table_size = 256M
   max_heap_table_size = 256M
   
   [client]
   socket=/data/mysql/lib/mysql.sock

5. 配置开机自启

   systemctl start mysqld.service
   systemctl enable mysqld.service

6. 查看 root 用户默认生成的长期明码

   grep "password" /var/log/mysqld.log

7. 应用默认明码登录 mysql

   mysql -u root -p

8. 批改 root 明码

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
   flush privileges;

附 1：常用命令

-- 创立用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 删除用户
DROP USER 'username'@'host';

-- 创立数据库
CREATE DATABASE databasename;

-- 删除数据库
DROP DATABASE databasename;

-- 用户受权
GRANT privileges ON databasename.tablename TO 'username'@'host';

-- 撤销用户权限
REVOKE privilege ON databasename.tablename FROM 'username'@'host';

#导出数据库
mysqldump -u root -p --databases databasename >/root/databasename.sql

#数据库单项同步（笼罩）mysqldump --host=sdbip --port=3306 -uroot -ppassword --set-gtid-purged=off --opt databasename | mysql --host=ddbip --port=3306 -uroot -ppassword -C databasename

附 2：重置明码

• 进行服务

  systemctl stop mysqld && systemctl status mysqld

• 批改 my.cnf 配置，增加 skip_grant_tables=1

• 启动服务，登录批改明码

  [root@192-168-1-207 pack]# mysql
  mysql> update mysql.user set authentication_string=password('password')   where user='root' and host='%';

​