详解MYSQL

作者: ropon 分类: 网站集群 发布时间: 2017-11-28 15:29
安装基本组件库
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脚本

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

发表评论

电子邮件地址不会被公开。 必填项已用*标注