乐趣区

关于mysql:MySQL57的安装与配置

jiezi

  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='%';

​

退出移动版