mysql 搭建过程

服务器安装

• 1、使用以下命令即可进行mysql安装，注意安装前先更新一下软件源以获得最新版本：

  $ sudo apt-get update #更新软件源
  $ sudo apt-get install mysql-server #安装mysql5.7

• 2、启动和关闭mysql服务器：

  $ service mysql start
  $ service mysql stop

• 3、确认是否启动成功，mysql节点处于LISTEN状态表示启动成功：

  $ sudo netstat -tap | grep mysql

• 4、进入mysql shell界面：

  $ mysql -u root -p
  没有初始密码的话，cat /etc/mysql/debian.cnf下面区查看，登陆后修改密码
  mysql -u debian-sys-maint -p
  Enter password:
  这里是关键点，由于mysql5.7没有password字段，密码存储在authentication_string字段中，password()方法还能用
  mysql> show databases；
  mysql> use mysql;
  mysql> update user set authentication_string=PASSWORD("xiaomu@2020") where user='root';
  mysql> update user set plugin="mysql_native_password";
  mysql> flush privileges;
  mysql> quit;

• 5、配置文件在/etc/mysql/mysql.conf.d/mysqld.cnf，可以根据自身需求进行配置

  service mysql restart

• 6、创建只读用户

  drop user 'xiaomu'@'%';
  create user 'xiaomu'@'%' identified by 'xiaomu@xxx';
  flush privileges;
  grant INSERT,SELECT,UPDATE,CREATE on . to 'xiaomu'@'%' identified by 'xiaomu@2020' WITH GRANT OPTION;
  flush privileges;

• 6.1 给指定用户授权指定数据库

grant all privileges on `test_db`.* to 'test'@'%' identified by '123456';

MySQL 8.0 以上版本使用下面的命令授权会报错：

mysql> grant all privileges on . to root@'%' identified by 'myslag123!@#';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘identified by ‘myslag123!@#’’ at line 1

因为新版的的mysql版本已经将创建用户和赋予权限的操作分开了，需要分两步操作：

1. 创建用户

mysql> create user 'root'@'%' identified by 'MySlag123!@#';
Query OK, 0 rows affected (0.01 sec)

2. 赋予权限

mysql> grant all privileges on . to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

• 6.2 视图授权给指定用户

-- create user 'library'@'%' identified by 'library@2023';
-- flush privileges;

grant select,show view on `HomepageOnclick`  to 'library'@'%';
grant select,show view on `HomeViewLog`  to 'library'@'%';
grant select,show view on `PaperViewLog`  to 'library'@'%';
grant select,show view on `SearchLog`  to 'library'@'%';
flush privileges;

• 7、查看用户

  use mysql;
  SELECT user,host FROM mysql.user;

  如果host不是%说明不能远程连接，需要为该用户设置下
  GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'xiaomu@2020' WITH GRANT OPTION;
  FLUSH PRIVILEGES;

• 8、mysql 配置文件/etc/mysql/mysql.conf.d/mysqld.cnf；数据存放/var/lib/mysql目录下

• 9、附mysqld.cnf配置

  [client]
  default-character-set=utf8mb4

  [mysql]
  default-character-set=utf8mb4

  [mysqld]
  port = 3306
  pid-file = /var/run/mysqld/mysqld.pid
  socket = /var/run/mysqld/mysqld.sock
  datadir = /var/lib/mysql

  default-time_zone='+8:00'
  character_set_server=utf8mb4
  collation-server=utf8mb4_unicode_ci
  character-set-client-handshake=FALSE
  init_connect='SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'

  skip-host-cache
  skip-name-resolve

  max_connections = 500
  max_connect_errors = 200
  open_files_limit = 8000
  max_allowed_packet = 500M
  max_binlog_size = 100M

  innodb_buffer_pool_size = 2000M
  innodb_log_file_size = 100M
  innodb_log_buffer_size = 8M
  innodb_flush_log_at_trx_commit = 1
  innodb_lock_wait_timeout = 50
  innodb_file_per_table

  net_read_timeout = 180
  net_write_timeout = 360

  wait_timeout = 604800
  interactive_timeout = 604800

  server-id=1
  log-bin=mysql-bin #从库会基于此log-bin来做复制
  binlog-format=ROW # 选择row模式
  binlog-do-db=label_data #用于读写分离的具体数据库
  binlog-do-db=label_db
  binlog_ignore_db=mysql #不用于读写分离的具体数据库
  binlog_ignore_db=information_schema #和binlog-do-db一样，可以设置多个

  slow_query_log=1
  skip-name-resolve
  thread_stack = 512k
  long_query_time=0.4
  bind-address = 0.0.0.0

  skip-log-bin # 关闭binlog

  expire_logs_days=5 # 保留指定天数的binlog

  !includedir /etc/mysql/conf.d/