安装MySQL-5.5.49流程及配置多实例
发表于:2024-10-18 作者:千家信息网编辑
千家信息网最后更新 2024年10月18日,下文内容主要给大家带来安装MySQL-5.5.49流程及配置多实例,所讲到的知识,与书籍略有不同,都是专业技术人员在与用户接触过程中,总结出来的,具有一定的经验分享价值,希望给广大读者带来帮助。安装M
千家信息网最后更新 2024年10月18日安装MySQL-5.5.49流程及配置多实例
下文内容主要给大家带来安装MySQL-5.5.49流程及配置多实例,所讲到的知识,与书籍略有不同,都是专业技术人员在与用户接触过程中,总结出来的,具有一定的经验分享价值,希望给广大读者带来帮助。
安装MySQL-5.5.49
安装包的获取:http://mirrors.sohu.com/mysql/
# 安装MySQL需要的依赖包yum install ncurses-devel libaio-devel -yrpm -qa ncurses-devel libaio-devel# 安装编译MySQL需要的软件yum install cmake -yrpm -qa cmake# 创建用户useradd mysql -u 777 -s /sbin/nologin -Mid mysql
上传MySQL包,解压
mkdir -p /home/oldboy/toolscd /home/oldboy/toolsrz -y #mysql-5.5.49.tar.gzll -shtar xf mysql-5.5.49.tar.gz cd mysql-5.5.49
编译安装
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.49 \-DMYSQL_DATADIR=/application/mysql-5.5.49/data \-DMYSQL_UNIX_ADDR=/application/mysql-5.5.49/tmp/mysql.sock \-DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=utf8_general_ci \-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \-DENABLED_LOCAL_INFILE=ON \-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DWITH_FEDERATED_STORAGE_ENGINE=1 \-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \-DWITH_FAST_MUTEXES=1 \-DWITH_ZLIB=bundled \-DENABLED_LOCAL_INFILE=1 \-DWITH_READLINE=1 \-DWITH_EMBEDDED_SERVER=1 \-DWITH_DEBUG=0make && make install && echo $?cd ..
创建软链接
ln -s /application/mysql-5.5.49/ /application/mysql
配置多实例
创建数据库文件目录,配置文件
mkdir -p /data/{3306,3307}/datatouch /data/{3306,3307}/{mysql,my.cnf}
tree /data//data/├── 3306#<==实例3306│ ├── data#<==3306数据库文件目录│ ├── my.cnf#<==3306配置文件│ └── mysql#<==3306启动脚本└── 3307#<==实例3307 ├── data#<==3307数据库文件目录 ├── my.cnf#<==3307配置文件 └── mysql#<==3307启动脚本
实例3306配置文件
vim /data/3306/my.cnf[client]port = 3306socket = /data/3306/mysql.sock[mysql]no-auto-rehash[mysqld]user = mysqlport = 3306socket = /data/3306/mysql.sockbasedir = /application/mysqldatadir = /data/3306/dataopen_files_limit = 1024back_log = 600max_connections = 800max_connect_errors = 3000table_cache = 614external-locking = FALSEmax_allowed_packet =8Msort_buffer_size = 1Mjoin_buffer_size = 1Mthread_cache_size = 100thread_concurrency = 2query_cache_size = 2Mquery_cache_limit = 1Mquery_cache_min_res_unit = 2k#default_table_type = InnoDBthread_stack = 192K#transaction_isolation = READ-COMMITTEDtmp_table_size = 2Mmax_heap_table_size = 2Mlong_query_time = 1#log_long_format#log-error = /data/3306/error.log#log-slow-queries = /data/3306/slow.logpid-file = /data/3306/mysql.pidlog-bin = /data/3306/mysql-binrelay-log = /data/3306/relay-binrelay-log-info-file = /data/3306/relay-log.infobinlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2Mexpire_logs_days = 7key_buffer_size = 16Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mbulk_insert_buffer_size = 1M#myisam_sort_buffer_size = 1M#myisam_max_sort_file_size = 10G#myisam_max_extra_sort_file_size = 10G#myisam_repair_threads = 1#myisam_recoverlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032,1062replicate-ignore-db=mysqlserver-id = 1innodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 32Minnodb_data_file_path = ibdata1:128M:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 4Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0[mysqldump]quickmax_allowed_packet = 2M[mysqld_safe]log-error=/data/3306/mysql_oldboy3306.errpid-file=/data/3306/mysqld.pid
实例3307配置文件
vim /data/3307/my.cnf[client]port = 3307socket = /data/3307/mysql.sock[mysql]no-auto-rehash[mysqld]user = mysqlport = 3307socket = /data/3307/mysql.sockbasedir = /application/mysqldatadir = /data/3307/dataopen_files_limit = 1024back_log = 600max_connections = 800max_connect_errors = 3000table_cache = 614external-locking = FALSEmax_allowed_packet =8Msort_buffer_size = 1Mjoin_buffer_size = 1Mthread_cache_size = 100thread_concurrency = 2query_cache_size = 2Mquery_cache_limit = 1Mquery_cache_min_res_unit = 2k#default_table_type = InnoDBthread_stack = 192K#transaction_isolation = READ-COMMITTEDtmp_table_size = 2Mmax_heap_table_size = 2M#long_query_time = 1#log_long_format#log-error = /data/3307/error.log#log-slow-queries = /data/3307/slow.logpid-file = /data/3307/mysql.pid#log-bin = /data/3307/mysql-binrelay-log = /data/3307/relay-binrelay-log-info-file = /data/3307/relay-log.infobinlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2Mexpire_logs_days = 7key_buffer_size = 16Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mbulk_insert_buffer_size = 1M#myisam_sort_buffer_size = 1M#myisam_max_sort_file_size = 10G#myisam_max_extra_sort_file_size = 10G#myisam_repair_threads = 1#myisam_recoverlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032,1062replicate-ignore-db=mysqlserver-id = 3innodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 32Minnodb_data_file_path = ibdata1:128M:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 4Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0[mysqldump]quickmax_allowed_packet = 2M[mysqld_safe]log-error=/data/3307/mysql_oldboy3307.errpid-file=/data/3307/mysqld.pid
实例3306启动脚本
vim /data/3306/mysql#!/bin/sh#################################################this scripts is created by oldboy at 2007-06-09#oldboy QQ:31333741#site:http://www.etiantian.org#blog:http://oldboy.blog.51cto.com#oldboy trainning QQ group: 208160987 226199307 44246017#################################################initport=3306mysql_user="root"mysql_pwd="oldboy"CmdPath="/application/mysql/bin"mysql_sock="/data/${port}/mysql.sock"#startup functionfunction_start_mysql(){ if [ ! -e "$mysql_sock" ];then printf "Starting MySQL...\n" /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null & else printf "MySQL is running...\n" exit fi}#stop functionfunction_stop_mysql(){ if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped...\n" exit else printf "Stoping MySQL...\n" ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown fi}#restart functionfunction_restart_mysql(){ printf "Restarting MySQL...\n" function_stop_mysql sleep 2 function_start_mysql}case $1 instart) function_start_mysql;;stop) function_stop_mysql;;restart) function_restart_mysql;;*) printf "Usage: /data/${port}/mysql {start|stop|restart}\n"esac
实例3307启动脚本
vim /data/3307/mysql#!/bin/sh#################################################this scripts is created by oldboy at 2007-06-09#oldboy QQ:31333741#site:http://www.etiantian.org#blog:http://oldboy.blog.51cto.com#oldboy trainning QQ group: 208160987 226199307 44246017#################################################initport=3307mysql_user="root"mysql_pwd="oldboy"CmdPath="/application/mysql/bin"mysql_sock="/data/${port}/mysql.sock"#startup functionfunction_start_mysql(){ if [ ! -e "$mysql_sock" ];then printf "Starting MySQL...\n" /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null & else printf "MySQL is running...\n" exit fi}#stop functionfunction_stop_mysql(){ if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped...\n" exit else printf "Stoping MySQL...\n" ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown fi}#restart functionfunction_restart_mysql(){ printf "Restarting MySQL...\n" function_stop_mysql sleep 2 function_start_mysql}case $1 instart) function_start_mysql;;stop) function_stop_mysql;;restart) function_restart_mysql;;*) printf "Usage: /data/${port}/mysql {start|stop|restart}\n"esac
修改启动脚本的权限
find /data -type f -name "mysql"|xargs chmod 700
授权mysql用户管理/data目录
chown -R mysql.mysql /data
初使化数据库
cd /application/mysql/scripts/./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/3306/data/./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/3307/data/
启动多实例,加入开机自启动
/data/3306/mysql start/data/3307/mysql startnetstat -tunlp |grep 330
# echo命令的 -e参数处理特殊字符;\n 换行且光标移至行首echo -e "\n##mysql multi instance\n/data/3306/mysql start\n/data/3307/mysql start">>/etc/rc.local
tail -3 /etc/rc.local##mysql multi instance/data/3306/mysql start/data/3307/mysql start
设置环境变量
方法一:将mysql的相关命令放入到已经是环境变量的目录下(推荐!!)
cp /application/mysql/bin/* /usr/local/bin/
方法二
export PATH=/application/mysql-5.5.49/bin/:$PATHecho 'export PATH=/application/mysql-5.5.49/bin/:$PATH' >>/etc/profiletail -1 /etc/profilesource /etc/profileecho $PATH
登录与退出多实例
多实例的登录(无密码)
mysql -S /data/3306/mysql.sockmysql -S /data/3307/mysql.sock
退出登录(4种)
mysql> exitmysql> quitmysql> Ctrl + cmysql> Ctrl + d
设置密码
mysqladmin -uroot password 'oldboy123' -S /data/3306/mysql.sockmysqladmin -uroot password 'oldboy123' -S /data/3307/mysql.sock
更改密码为:oldboy456
mysqladmin -uroot -poldboy123 password 'oldboy456' -S /data/3306/mysql.sock
多实例的登录(有密码)
mysql -uroot -poldboy123 -S /data/3306/mysql.sockmysql -uroot -poldboy123 -S /data/3307/mysql.sock
再加一个实例3308
mkdir /data/3308/data -pcp /data/3306/{my.cnf,mysql} /data/3308/sed -i 's#3306#3308#g' /data/3308/{my.cnf,mysql}sed -i 's#server-id = 1#server-id = 8#g' /data/3308/my.cnfchown -R mysql.mysql /data/3308chmod 700 /data/3308/mysqlcd /application/mysql-5.5.49/scripts/./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/3308/data//data/3308/mysql startecho "/data/3308/mysql start" >>/etc/rc.localnetstat -tunpl |grep 330mysql -S /data/3308/mysql.sockmysqladmin -uroot password 'oldboy123' -S /data/3308/mysql.sockmysql -uroot -poldboy123 -S /data/3308/mysql.sock
MySQL多实例优化
清理无用的MySQL库(3306)
mysql -uroot -poldboy123 -S /data/3306/mysql.sockdrop database test;show databases;
清理无用的MySQL用户
select user,host from mysql.user;drop user "root"@"::1";drop user " "@"db02"; drop user "root"@"db02";drop user " "@"localhost";
故障:drop命令删除不了用户
可能是大写及Linux主机名导致的,注意:删除用户尽量用drop命令,不要用delete命令
解决方法如下:
delete from mysql.user where user='' and host='S1'; delete from mysql.user where user='root' and host='S1';
刷新权限
刷新权限,使更改立马生效
flush privileges;
对于以上关于安装MySQL-5.5.49流程及配置多实例,如果大家还有更多需要了解的可以持续关注我们的行业推新,如需获取专业解答,可在官网联系售前售后的,希望该文章可给大家带来一定的知识更新。
实例
配置
文件
用户
命令
目录
脚本
密码
数据
数据库
登录
方法
权限
流程
专业
变量
环境
知识
编译
不同
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
万盛网络安全与信息化
安卓手机代理服务器
服务器的防护级别
pg数据库如何建表加注释
维护网络安全不应有
西南软件开发专业大学排名
信息网络安全检查工作会议
sql数据库world文档导入
u8移服务器
南京网络安全准入控制系统售价
5g网络技术高级资格证书
青海惠普服务器维修维保哪家便宜
数据库系统概论第5版第二章测试
ntp时间服务器ip地址
电子商务的数据库技术与进化
网络安全与管理专业专科
新浪爱彩网络技术
服务器系统日志删除后可以恢复吗
乐玛士 软件开发
vba清空表格数据库数据
高并发数据库出租
数据库课程设计感谢信
海北州网络技术费用
网络技术专业人员匮乏
云服务器图片显示不出来
宙斯盾网络安全隔离卡
网络安全租用云
网络安全宣传周班会主题
中宣部网络安全办公室
网络安全与管理专业专科