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/