详解MYSQL

安装基本组件库

yum -y install gcc-c++ perl ncurses ncurses-devel libaio numactl numactl-libs perl-Module-Install.noarch net-tools vim wget

mysql

1、创建mysql普通用户

id -u mysql >/dev/null 2>&1

[ $? -ne 0 ] && useradd mysql -s /sbin/nologin -M -g mysql

[ ! -d /home/ropon/tools ] && mkdir -p /home/ropon/tools

cd /home/ropon/tools

wget -c https://cmake.org/files/v3.6/cmake-3.6.2.tar.gz (mysql编译工具)

tar zxvf cmake-3.6.2.tar.gz

cd cmake-3.6.2

./configure

make && make install

wget -c http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.38.tar.gz

[ ! -d /usr/local/mysql-5.6.38 ] && mkdir -p /usr/local/mysql-5.6.38 #创建安装目录

[ ! -d /home/mysql ] && mkdir -p /home/mysql #创建数据库存放目录

chown mysql.mysql -R /home/mysql

ln -s /usr/local/mysql-5.6.38 /usr/local/mysql

cd /home/ropon/tools

tar zxvf mysql-5.6.38.tar.gz

cd mysql-5.6.38

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ #安装目录

-DMYSQL_DATADIR=/home/mysql \ #数据库存放目录

-DSYSCONFDIR=/etc #配置文件目录

-DWITH_INNOBASE_STORAGE_ENGINE=1 \ #安装INNOBASE存储引擎

-DWITH_PARTITION_STORAGE_ENGINE=1 \ #安装数据库分区

-DWITH_FEDERATED_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ #安装BLACKHOLE存储引擎

-DWITH_MYISAM_STORAGE_ENGINE=1 \ #安装MYISAM存储引擎

-DWITH_EMBEDDED_SERVER=1 \

-DENABLE_DTRACE=0 \

-DENABLED_LOCAL_INFILE=1 \

-DDEFAULT_CHARSET=utf8mb4 \

-DDEFAULT_COLLATION=utf8mb4_general_ci \

-DEXTRA_CHARSETS=all \

-DCMAKE_EXE_LINKER_FLAGS=’-ljemalloc’

以上可选

make -j 4 #多进程编译,4这个数值请修改cpu核心数

make install

/bin/cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld #拷贝启动项

sed -i “s@^basedir=.*@basedir=/usr/local/mysql@” /etc/init.d/mysqld #修改启动项mysql安装目录

sed -i “s@^datadir=.*@datadir=/home/mysql@” /etc/init.d/mysqld #修改启动项mysql存放目录

chmod +x /etc/init.d/mysqld

chkconfig –add mysqld && chkconfig mysqld on #给执行权限添加开机自启

优化my.cnf

[client]

port = 3306

socket = /tmp/mysql.sock

default-character-set = utf8mb4

[mysql]

prompt=”MySQL [\\d]> “ #prompt命令可以在mysql提示符中显示当前用户、数据库、时间等信息

#prompt=”MySQL [\\u@\\h:\\d]> “

#\\u 当前用户 \\h 当前链接地址 \\d 当前数据库

no-auto-rehash #命令自动补全的意思

[mysqld]

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /home/mysql

pid-file = /home/mysq/mysql.pid

user = mysql

bind-address = 0.0.0.0

server-id = 1

init-connect = ‘SET NAMES utf8mb4’

character-set-server = utf8mb4

skip-name-resolve #禁止掉DNS的查询

#mysql会在用户登录过程中对客户端IP进行DNS反查,不管你是使用IP登录还是域名登录,这个反查#的过程都是在的。所以如果你的mysql所在的服务器的DNS有问题或者质量不好,那么就有可能造成#我遇到的这个问题,DNS解析出现问题。

#skip-networking #开启该选项后就不能远程访问MySQL

back_log = 300

#指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就#是说,如果MySql的连接数达到max_connections时,新来的请求将会被存

#在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量#超过back_log,将不被授予连接资源。

max_connections = 1000

max_connect_errors = 6000

#当此值设置为10时,意味着如果某一客户端尝试连接此MySQL服务器,但是失败(如密码错误等 #)10次,则MySQL会无条件强制阻止此客户端连接。如果希望重置此计数器的值,则必须重启#MySQL服务器或者执行Mysql> FLUSH HOSTS;

open_files_limit = 65535 #打开多少文件描述符

table_open_cache = 128

max_allowed_packet = 500M

binlog_cache_size = 1M

max_heap_table_size = 8M

tmp_table_size = 16M

read_buffer_size = 2M

read_rnd_buffer_size = 8M #MySQL的随机读缓冲区大小

sort_buffer_size = 8M

join_buffer_size = 8M

key_buffer_size = 4M

thread_cache_size = 8

query_cache_type = 1 #查询总是先到查询缓存中查找,即使使用了sql_no_cache仍然查询缓存,

#因为sql_no_cache只是不缓存查询结果,而不是不使用查询结果。

query_cache_size = 8M

query_cache_limit = 2M

#ft_min_word_len = 4 #配置最小搜索长度

#从 Mysql 4.0 开始就支持全文索引功能,但是 Mysql 默认的最小索引长度是 4。如果是#英文默认值是比较合理的,但是中文绝大部分词都是2个字符,这就导致小于4个字的词#都不能被索引,全文索引功能就形同虚设了。国内的空间商大部分可能并没有注意到这#个问题,没有修改 Mysql 的默认设置。

log_bin = mysql-bin

binlog_format = mixed

expire_logs_days = 7

log_error = /home/mysql/mysql-error.log

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /home/mysql/mysql-slow.log

performance_schema = 0

explicit_defaults_for_timestamp

#lower_case_table_names = 1

skip-external-locking #跳过外部锁定

#当外部锁定(external-locking)起作用时,每个进程若要访问数据表,则必须等待之前的进程完成

#操作并解除锁定。由于服务器访问数据表时经常需要等待解锁,因此在单服务器环境下external #locking会让MySQL性能下降。所以在很多Linux发行版的源中,MySQL配置文件中默认使用了skip-#external-locking来避免external locking。

default_storage_engine = InnoDB

innodb_file_per_table = 1

innodb_open_files = 500

innodb_buffer_pool_size = 64M

innodb_write_io_threads = 4

innodb_read_io_threads = 4

innodb_thread_concurrency = 0

innodb_purge_threads = 1

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 2M

innodb_log_file_size = 32M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M

myisam_sort_buffer_size = 8M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

interactive_timeout = 28800

wait_timeout = 28800

[mysqldump]

quick

max_allowed_packet = 500M

[myisamchk]

key_buffer_size = 8M

sort_buffer_size = 8M

read_buffer = 4M

mysql_install_dir=/usr/local/mysql

mysql_data_dir=/home/mysql

dbrootpwd=west263.com

${mysql_install_dir}/scripts/mysql_install_db –user=mysql –basedir=${mysql_install_dir} –datadir=${mysql_data_dir}

/bin/cp ${mysql_install_dir}/support-files/mysql.server /etc/init.d/mysqld

sed -i “s@^basedir=.*@basedir=${mysql_install_dir}@” /etc/init.d/mysqld

sed -i “s@^datadir=.*@datadir=${mysql_data_dir}@” /etc/init.d/mysqld

chmod +x /etc/init.d/mysqld

chkconfig –add mysqld

chkconfig mysqld on

service mysqld start

[ -z “$(grep ^’export PATH=’ /etc/profile)” ] && echo “export PATH=${mysql_install_dir}/bin:\$PATH” >> /etc/profile

[ -n “$(grep ^’export PATH=’ /etc/profile)” -a -z “$(grep ${mysql_install_dir} /etc/profile)” ] && sed -i “s@^export PATH=\(.*\)@export PATH=${mysql_install_dir}/bin:\1@” /etc/profile

. /etc/profile

${mysql_install_dir}/bin/mysql -e “grant all privileges on *.* to root@’127.0.0.1’ identified by \“${dbrootpwd}\“ with grant option;”

${mysql_install_dir}/bin/mysql -e “grant all privileges on *.* to root@’localhost’ identified by \“${dbrootpwd}\“ with grant option;”

${mysql_install_dir}/bin/mysql -uroot -p${dbrootpwd} -e “delete from mysql.user where Password=’’;”

${mysql_install_dir}/bin/mysql -uroot -p${dbrootpwd} -e “delete from mysql.db where User=’’;”

${mysql_install_dir}/bin/mysql -uroot -p${dbrootpwd} -e “delete from mysql.proxies_priv where Host!=’localhost’;”

${mysql_install_dir}/bin/mysql -uroot -p${dbrootpwd} -e “drop database test;”

${mysql_install_dir}/bin/mysql -uroot -p${dbrootpwd} -e “reset master;”

http://luopengtest.gotoip2.com/shell/lnmp/mysql.sh mysql自动安装shell脚本