MySQL5.7的安装与配置

  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. 修改目录权限

    chown mysql.mysql /data/mysql/ -R
    chown mysql.mysql /var/run/mysqld -R
  6. 配置开机自启

    systemctl start mysqld.service
    systemctl enable mysqld.service
  1. 查看root用户默认生成的临时密码

    grep "password" /var/log/mysqld.log
  2. 使用默认密码登录mysql

    mysql -u root -p
  3. 设置密码,开启远程访问

    SET PASSWORD = PASSWORD('NewPassword');
    UPDATE mysql.user SET user.Host='%' where user.User='root';
    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='%';

​

评论

暂无评论