安装MySQL-5.5.49流程及配置多实例
发表于:2024-10-22 作者:千家信息网编辑
千家信息网最后更新 2024年10月22日,下文内容主要给大家带来安装MySQL-5.5.49流程及配置多实例,所讲到的知识,与书籍略有不同,都是专业技术人员在与用户接触过程中,总结出来的,具有一定的经验分享价值,希望给广大读者带来帮助。安装M
千家信息网最后更新 2024年10月22日安装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安全错误
数据库的锁怎样保障安全
软件开发公司资金往来多吗
如何破解微信网络安全协议
福建省云服务器虚拟主机
mt4怎么搜不到服务器
网络安全供应链安全防范能力
供应商数据库是什么意思
地球地形数据库战时应用
网络技术安全资格证书
软件开发过程必备的文档
漯河软件开发方案
服务器终端机指什么
qt获取ftp服务器下某个文件
海南电商软件开发技术
2016梦幻服务器排名
国外的服务器如何备案
湖北抖图字节互联网科技有限公司
app软件开发薪资
网络安全法的保证
组织家庭无线网络技术
数据库的十个应用领域
软件文档是整个软件开发工作
服务器 不停发包
服务器可以找回管理员账号密码吗
章鱼风云互联网科技有限公司
湖北通信软件开发设施服务标准
山西百信服务器虚拟主机
数据库理论方法有哪些
数据库系统的安全管理系统
河源通信软件开发代理价格
张家口网络服务器机柜代理