• 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;
      
    • 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/
  • 相关阅读:
    青云面试记录
    设计模式01 简介
    枚举
    MSIL语言类型对照表
    爬虫练习一
    项目管理学习理解
    成本管理4大过程
    项目范围
    范围管理7大大过程
    合同管理
  • 原文地址:https://www.cnblogs.com/beihangxuwei/p/13447969.html
Copyright © 2020-2023  润新知