MySQL Database Data directory Migration and configuration optimization under CentOS

Directory Migration

2. Shutting down the database service

   service mysqld stop

4. Copy Database

   mv  /var/lib/mysql /data/mysql# 或者使用cp -a复制# 这两个命令都会带权限到新目录去

6. Modifying a configuration file/etc/my.cnf

   [mysqld]#datadir=/var/lib/mysql                      ------原系统默认路径datadir=/data/mysql      ------现有路径#socket=/var/lib/mysql/mysql.sock            ------原socket路径现socket=/data/mysql/mysql.sock                ------现有路径[mysqld_safe]socket=/data/mysql/mysql.sock                 -----现有路径[client]socket=/data/mysql/mysql.sock                 -----现有路径[mysql.server]socket=/data/mysql/mysql.sock                 -----现有路径

8. Restart Service

   service mysqld start

10. Problems that could not be started after the migration directory, it is possible that the SELinux ghost, can be set to permissive mode

    # getenforceEnforcing# setenforce 0# getenforcePermissive# setenforce 1 设置SELinux 成为enforcing模式# setenforce 0 设置SELinux 成为permissive模式

    or close selinux,vi/etc/selinux/config Modify selinux=disabled

    # This file controls the state of SELinux on the system.# SELINUX= can take one of these three values:#     enforcing - SELinux security policy is enforced.#     permissive - SELinux prints warnings instead of enforcing.#     disabled - No SELinux policy is loaded.SELINUX=disabled# SELINUXTYPE= can take one of these two values:#     targeted - Targeted processes are protected,#     mls - Multi Level Security protection.SELINUXTYPE=targeted

Configuration Optimizations

Yum installed mysql default configuration does not seem to meet the requirements, mainly modify the following configuration

# 批量执行语句包的大小max_allowed_packet = 16M# innodb缓存，设置为服务器内存的 80%左右innodb_buffer_pool_size = 2048M# 锁等待超时时间innodb_lock_wait_timeout = 30# 0时表示关闭，1时表示打开，2表示只要select 中明确指定SQL_CACHE才缓存query_cache_type = 0# 最大连接数max_connections = 1024# 下面两个参数一起设定innodb_flush_log_at_trx_commit = 2sync_binlog = 1000# 表不区分大小写lower_case_table_names = 1

MySQL Database Data directory Migration and configuration optimization under CentOS