Mysql 5.7 初次安装使用配置

2016年9月7日

MySQL5.7在安装完后，第一次启动时，会在root目录下生产一个随机的密码，文件名为

.mysql_secret

登录时需要用随机密码登录，然后通过以下命令修改密码

  1. SET PASSWORD = PASSWORD('new password');

************************************************************************
多实例

  1. mysql -uroot -P3307 -S /home/data/mysql/3307/data/mysql.sock -p
  2. SET PASSWORD = PASSWORD('E*8i58kEuspRNJZPyXdNy2mQ');

创建用户

  1. create user [email protected] identified by 'E*8i58kEuspRNJZPyXdNy2mQ';
  2. GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;
  3. create user [email protected] identified by 'root';
  4. GRANT PROCESS ON *.* TO 'zabbix'@'127.0.0.1' identified BY 'zabbix';
  5. GRANT PROCESS ON *.* TO 'zabbix_monitor'@'localhost' identified BY 'root';

 

一、创建一个用户

  1. create user xxxxx@localhost identified by 'xxxxxxx';

二、开启mysql远程连接

进入mysql

  1. mysql -u root -p mysql # 第1个mysql是执行命令，-u 用户名 -p root密码

在mysql控制台执行:

  1. grant all privileges on *.* to 'test'@'%' identified by 'xxxxxxx' with grant option; # 下面会详细介绍grant 命令
  2. # test是用户名，%代表任意主机，'xxxxxxx'指定的登录密码（这个和本地的root密码可以设置不同的，互不影响）
  3. flush privileges;# 重载系统权限

允许3306端口

  1. iptables -I INPUT -p tcp -m state --state NEW -m tcp --dport 3306-j ACCEPT
  2. # 查看规则是否生效
  3. iptables -L -n # 或者: service iptables status
  4. # 此时生产环境是不安全的，远程管理之后应该关闭端口，删除之前添加的规则
  5. 上面iptables添加/删除规则都是临时的，如果需要重启后也生效，需要保存修改:
  6. service iptables save # 或者: /etc/init.d/iptables save
  7. vi /etc/sysconfig/iptables # 加上下面这行规则也是可以的
  8. -A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
  9. /usr/bin/mysqladmin -uroot password "xxxxxxxx'
  10. /usr/bin/mysql_secure_installation

三、创建用户权限

  1. grant select on dmc_db.* to admin; 赋予用户查询权限 dmc_db是库名称 * 可以指定表的权限 admin 是用户 这句户就是说给 admin这个用户赋予 dmc_db 这个库所有的查询权限
  2. grant update on dmc_db.* to admin; 赋予用户更新权限
  3. grant insert on dmc_db.* to admin; 赋予用户插入权限
  4. grant delete on dmc_db.* to admin; 赋予用户删除权限
  5. grant select,update,insert,delete on dmc_db.* to admin; 一次性赋予增删改查权限
  6. grant select,update,insert,delete on dmc_db.* to 'test'@'%' identified by 'xxxxxxx'; # 这句户是指 开启远程主机 并且指定密码

四、 回收远程主机的权限

  1. revoke all privileges on *.* from test;
  2. update user set authentication_string=password('123qwe') where user='root' and Host = 'localhost'; # mysql5.7 修改root密码

更改密码

  1. update mysql.user set authentication_string=password('asdf123123') where user='zabbix' and Host = 'localhost';
  2. flush privileges;

一份 my.cnf 的配置

  1. [client]
  2. port=3306
  3. socket = /var/lib/mysql/mysql.sock
  4. default-character-set=utf8
  6. [mysqld]
  7. ########basic settings########
  9. datadir=/var/lib/mysql
  10. socket=/var/lib/mysql/mysql.sock
  12. # Disabling symbolic-links is recommended to prevent assorted security risks
  13. symbolic-links=0
  15. log-error=/var/log/mysqld.log
  16. pid-file=/var/run/mysqld/mysqld.pid
  18. server-id = 20160907
  20. bind_address = 0.0.0.0
  21. autocommit = 0
  22. skip_name_resolve = 1
  23. max_connections = 800
  24. max_connect_errors = 1000
  26. transaction_isolation = READ-COMMITTED
  27. explicit_defaults_for_timestamp = 1
  28. join_buffer_size = 134217728
  29. tmp_table_size = 67108864
  30. tmpdir = /tmp
  31. max_allowed_packet = 16777216
  32. sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
  33. interactive_timeout = 1800
  34. wait_timeout = 1800
  35. read_buffer_size = 16777216
  36. read_rnd_buffer_size = 33554432
  37. sort_buffer_size = 33554432
  38. ########log settings########
  39. log-error = /home/data/mysql/3307/data/error.log
  40. slow_query_log = 1
  41. slow_query_log_file = /home/data/mysql/3307/data/slow.log
  42. log_queries_not_using_indexes = 1
  43. log_slow_admin_statements = 1
  44. log_slow_slave_statements = 1
  45. log_throttle_queries_not_using_indexes = 10
  46. expire_logs_days = 90
  47. long_query_time = 2
  48. min_examined_row_limit = 100
  49. ########replication settings########
  50. master_info_repository = TABLE
  51. relay_log_info_repository = TABLE
  53. log_bin = mysql-bin.log
  54. binlog_format = row
  56. sync_binlog = 1
  57. gtid_mode = on
  58. enforce_gtid_consistency = 1
  59. log_slave_updates
  61. relay_log = relay.log
  62. relay_log_recovery = 1
  63. binlog_gtid_simple_recovery = 1
  64. slave_skip_errors = ddl_exist_errors
  65. ########innodb settings########
  67. [mysqld-5.7]
  68. innodb_buffer_pool_dump_pct = 40
  69. innodb_page_cleaners = 4
  70. innodb_undo_log_truncate = 1
  71. innodb_max_undo_log_size = 2G
  72. innodb_purge_rseg_truncate_frequency = 128
标签：、

没有评论

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注