ubuntu修改mysql默认路径和修改max_connect参数

骑猪看日落 2022-06-08 23:48 238阅读 0赞

由于老需要搭建不同区域的mysql，而RDS有的时候又不想用。原因当然是比自己搭建的贵啦不少啦= =。

记录下正在使用的mysql5.7配置：

#
    # The MySQL database server configuration file.
    #
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    # 
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    # This will be passed to all mysql clients
    # It has been reported that passwords should be enclosed with ticks/quotes
    # escpecially if they contain "#" chars...
    # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
    
    # Here is entries for some specific programs
    # The following values assume you have at least 32M ram
    
    [mysqld_safe]
    socket		= /var/run/mysqld/mysqld.sock
    nice		= 0
    
    [mysqld]
    #sql_mode =STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    #sql_mode = NO_ENGINE_SUBSTITUTION
    #
    # * Basic Settings
    #
    user		= mysql
    pid-file	= /var/run/mysqld/mysqld.pid
    socket		= /var/run/mysqld/mysqld.sock
    port		= 3306
    basedir		= /data/mysql
    datadir		= /data/mysql
    tmpdir		= /tmp
    lc-messages-dir	= /usr/share/mysql
    skip-external-locking
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    #bind-address		= 127.0.0.1
    #
    # * Fine Tuning
    #
    key_buffer_size		= 200M
    max_allowed_packet	= 16M
    low_priority_updates=1
    thread_stack		= 192K
    thread_cache_size       = 8
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover-options  = BACKUP
    #最大连接数
    max_connections        = 4000
    #table_cache            = 64
    #table缓存
    table_open_cache=2000
    table_open_cache_instances=16
    #thread_concurrency     = 10
    #
    # * Query Cache Configuration
    #
    #查询缓存
    query_cache_limit	= 128M
    query_cache_type=1
    query_cache_size        = 528M
    
    innodb_file_per_table
    innodb_log_files_in_group=3
    innodb_open_files=4000
    
    #http://blog.csdn.net/qing_gee/article/details/42742241
    innodb_buffer_pool_size=2G
    innodb_buffer_pool_instances=1
    #http://blog.csdn.net/wei_wenbo/article/details/50817751
    join_buffer_size=32K
    sort_buffer_size=32K
    innodb_checksums=0
    innodb_doublewrite=0
    innodb_support_xa=0
    innodb_thread_concurrency=0
    innodb_flush_log_at_trx_commit=2
    innodb_max_dirty_pages_pct=50
    innodb_use_native_aio=1
    innodb_stats_persistent = 1
    innodb_adaptive_flushing = 1
     innodb_flush_neighbors = 0
    innodb_read_io_threads = 4
     innodb_write_io_threads = 4
     innodb_io_capacity = 4000
     innodb_purge_threads=1
    innodb_adaptive_hash_index=0
     innodb_monitor_enable = '%'
     performance_schema=OFF
    #innodb_spin_wait_delay= 6 / 96
    #innodb_additional_mem_pool_size=20M
    #innodb_log_file_size=1024M
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    # As of 5.1 you can enable the log at runtime!
    #general_log_file        = /var/log/mysql/mysql.log
    #general_log             = 1
    #
    # Error log - should be very few entries.
    #
    log_error = /data/mysql/sql_error.log
    #
    # Here you can see queries with especially long duration
    #log_slow_queries	= /var/log/mysql/mysql-slow.log
    #long_query_time = 2
    #log-queries-not-using-indexes
    #
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    #       other settings you may need to change.
    #server-id		= 1
    #log_bin			= /data/mysql/logs/mysql-bin.log
    #expire_logs_days	= 10
    max_binlog_size   = 100M
    #binlog_do_db		= 
    #binlog_ignore_db	= include_database_name
    #
    # * InnoDB
    #
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    #
    # * Security Features
    #
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    #
    # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
    #
    # ssl-ca=/etc/mysql/cacert.pem
    # ssl-cert=/etc/mysql/server-cert.pem
    # ssl-key=/etc/mysql/server-key.pem

以上配置有的是博主从各个业务场景需求中添加的，有的是网上查询的配置，不一定是最优的，但在测试和较小的并发下效果还是不错的。

这里有些地方需要注意：baseDir和max\_connect被修改过，这两个值也是需要点额外操作的，直接修改无效。记录一下因为查资料了很多次。博主只会ubuntu，所以以ubuntu为主。

设置mysql存放目录

#1. 设置新的数据存放路径，：
    mkdir -p /data/mysql 
    #2.复制原有数据
    cp -R /var/lib/mysql/* /data/mysql 
    #3.修改权限
    chown -R mysql:mysql /data/mysql 
    #4.修改配置文件
    vim /etc/mysql/my.cnf
    datadir  = /data/mysql
    #5.修改启动文件
    vim /etc/apparmor.d/usr.sbin.mysqld
    #把
    /var/lib/mysql r
    /var/lib/mysql/** rwk
    #修改成
    /data/mysql r
    /data/mysql/** rwk, 
    #6.重启服务，即重启apparmor
    /etc/init.d/apparmor restart
    /etc/init.d/mysql restart

设置max\_connect，这个值与很多相关，包括ubuntu设置的最大打开文件数等，设置请看《 [新开ubuntu的必要优化][ubuntu] 》。

然后设置mysql.service文件：

vi /lib/systemd/system/mysql.service

最后增加两行：

LimitNOFILE=102400
    LimitNPROC=102400

运行systemctl daemon-reload

再进入mysql检查下

show variables like "max_connections";

![Center][]

[ubuntu]: http://blog.csdn.net/weixin_39678127/article/details/77334724
[Center]: /images/20220609/7fe9ae1c65814869b0ecf3f48b8765bb.png