MySQL 5.6对已有Mysql单实例的机器,再添加mysql数据库,实现单机多实例
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL 5.6对已有Mysql单实例的机器,再添加mysql数据库,实现单机多实例,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含11412字,纯文字阅读大概需要17分钟。
内容图文
一、需求:
对已有Mysql单实例的机器,再添加两个mysql数据库,实现单机多实例。
一个绑定在端口3306,另外两个绑定在端口3307,3308;
数据分别存放在/data/mysqldata、/data/mysqldata2、/data/mysqldata3
三个实例均采用InnoDB作为默认的存储引擎,字符编码采用UTF-8;
三个实例均采用相同的性能优化配置参数;
MySQL的源码安装请看我的另一篇博客http://yylinux.blog.51cto.com/8831641/1677165
二、创建支持多实例的配置文件
1、创建所需目录
单实例时存放数据目录/data/mysqldata已经存在
现在创建另外两个目录
[root@MySQL ~]# mkdir /data/mysqldata2 mysqldata3
[root@MySQL ~]# cd /app/mysql/
[root@MySQL mysql]# ls
bin binlogs COPYING data docs include INSTALL-BINARY lib log man my.cnf my-new.cnf mysql-test README run scripts share sql-bench support-files tmp
查看 log、binlogs、tmp、run 目录是否存在,如果不存在请创建
编辑my.cnf 配置文件
[root@MySQL ~]#vim /etc/my.cnf
-----
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It‘s a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
default-character-set = utf8
[mysqld_multi]
mysqld = /app/mysql/bin/mysqld_safe
mysqladmin = /app/mysql/bin/mysqladmin
log = /app/mysql/log/mysqld_multi.log
user = root
#password =
# This is the general purpose database.
# The locations are default.
# They are left in [mysqld] in case the server is started normally instead of by mysqld_multi.
[mysqld1]
socket = /app/mysql/run/mysqld.sock
port = 3306
pid-file = /app/mysql/run/mysqld.pid
datadir = /data/mysqldata
lc-messages-dir = /app/mysql/share/english
# These support master - master replication
#auto-increment-increment = 4
#auto-increment-offset = 1 # Since it is master 1
#log-bin = /app/mysql/binlogs/bin-log-mysqld1
#log-bin-index = /app/mysql/binlogs/bin-log-mysqld1.index
#binlog-do-db = # Leave this blank if you want to control it on slave
#max_binlog_size = 1024M
[mysqld2]
socket = /app/mysql/run/mysqld.sock2
port = 3307
pid-file = /app/mysql/run/mysqld.pid2
datadir = /data/mysqldata2
lc-messages-dir = /app/mysql/share/english
# These support master - master replication
#auto-increment-increment = 4
#auto-increment-offset = 1 # Since it is master 1
log-bin = /app/mysql/binlogs/bin-log-mysqld2
log-bin-index = /app/mysql/binlogs/bin-log-mysqld2.index
#binlog-do-db = # Leave this blank if you want to control it on slave
max_binlog_size = 1024M
[mysqld3]
socket = /app/mysql/run/mysqld.sock3
port = 3308
pid-file = /app/mysql/run/mysqld.pid3
datadir = /data/mysqldata3
lc-messages-dir = /app/mysql/share/english
# These support master - master replication
#auto-increment-increment = 4
#auto-increment-offset = 1 # Since it is master 1
log-bin = /app/mysql/binlogs/bin-log-mysqld3
log-bin-index = /app/mysql/binlogs/bin-log-mysqld3.index
#binlog-do-db = # Leave this blank if you want to control it on slave
max_binlog_size = 1024M
[mysqld]
basedir = /app/mysql
tmpdir = /app/mysql/tmp
socket = /app/mysql/run/mysqld.sock
port = 3306
pid-file = /app/mysql/run/mysqld.pid
datadir = /app/mysql/data
lc-messages-dir = /app/mysql/share/english
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
# Increase the max connections
max_connections = 2
# The expiration time for logs, including binlogs
expire_logs_days = 14
# Set the character as utf8
character-set-server = utf8
collation-server = utf8_unicode_ci
# This is usually only needed when setting up chained replication
#log-slave-updates
# Enable this to make replication more resilient against server crashes and restarts
# but can cause higher I/O on the server
#sync_binlog = 1
# The server id, should be unique in same network
server-id = 1
# Set this to force MySQL to use a particular engine/table-type for new tables
# This setting can still be overridden by specifying the engine explicitly
# in the CREATE TABLE statement
default-storage-engine = INNODB
# Enable Per Table Data for InnoDB to shrink ibdata1
innodb_file_per_table = 1
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /data/mysqldata
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /data/mysqldata
# You can set .._buffer_pool_size up to 50 - 80 % of RAM
# but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
[mysqlhotcopy]
interactive-timeout
[mysql.server]
user = mysql
[mysqld_safe]
log-error = /app/mysql/log/mysqld.log
pid-file = /app/mysql/run/mysqld.pid
open-files-limit = 8192
------
三、初始化数据库
[root@MySQL ~]# cd /app/mysql/scripts/
[root@MySQL scripts]#./mysql_install_db --basedir=/app/mysql --user=mysql --datadir=/data/mysqldata2/
[root@MySQL scripts]#./mysql_install_db --basedir=/app/mysql --user=mysql --datadir=/data/mysqldata3/
四、创建mysqld_multi.server脚本
[root@MySQL ~]# cd /opt/mysql-5.6.23/support-files/
[root@MySQL support-files]# cp -p mysqld_multi.server /etc/init.d/mysqld_multid
编辑mysqld_multid脚本
[root@MySQL ~]# vim /etc/init.d/mysqld_multid
#!/bin/sh
#
# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.
# This script assumes that my.cnf file exists either in /etc/my.cnf or
# /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the
# mysqld_multi documentation for detailed instructions.
#
# This script can be used as /etc/init.d/mysql.server
#
# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 64 36
# description: A very fast and reliable SQL database engine.
#
# Version 1.0
#
basedir=/app/mysql
bindir=/app/mysql/bin
conf=/etc/my.cnf
export PATH=$PATH:$bindir
if test -x $bindir/mysqld_multi
then
mysqld_multi="$bindir/mysqld_multi";
else
echo "Can‘t execute $bindir/mysqld_multi from dir $basedir";
exit;
fi
case "$1" in
‘start‘ )
"$mysqld_multi" --defaults-extra-file=$conf start $2
;;
‘stop‘ )
"$mysqld_multi" --defaults-extra-file=$conf stop $2
;;
‘report‘ )
"$mysqld_multi" --defaults-extra-file=$conf report $2
;;
‘restart‘ )
"$mysqld_multi" --defaults-extra-file=$conf stop $2
"$mysqld_multi" --defaults-extra-file=$conf start $2
;;
*)
echo "Usage: $0 {start|stop|report|restart}" >&2
;;
esac
五、mysql实例管理
启动mysql实例
[root@MySQL ~]# /etc/init.d/mysqld_multid start 1,2,3
或
[root@MySQL ~]# /etc/init.d/mysqld_multid start 3306,3307,3308
[root@MySQL ~]# ps -ef | grep mysqld | grep -v grep
root 6528 1 0 17:44 pts/0 00:00:00 /bin/sh /app/mysql/bin/mysqld_safe --socket=/app/mysql/run/mysqld.sock --port=3306 --pid-file=/app/mysql/run/mysqld.pid --datadir=/data/mysqldata --lc-messages-dir=/app/mysql/share/english --socket=/app/mysql/run/mysqld.sock --port=3306 --pid-file=/app/mysql/run/mysqld.pid --datadir=/data/mysqldata --lc-messages-dir=/app/mysql/share/english
root 6534 1 0 17:44 pts/0 00:00:00 /bin/sh /app/mysql/bin/mysqld_safe --socket=/app/mysql/run/mysqld.sock2 --port=3307 --pid-file=/app/mysql/run/mysqld.pid2 --datadir=/data/mysqldata2 --lc-messages-dir=/app/mysql/share/english --log-bin=/app/mysql/binlogs/bin-log-mysqld2 --log-bin-index=/app/mysql/binlogs/bin-log-mysqld2.index --max_binlog_size=1024M --socket=/app/mysql/run/mysqld.sock2 --port=3307 --pid-file=/app/mysql/run/mysqld.pid2 --datadir=/data/mysqldata2 --lc-messages-dir=/app/mysql/share/english --log-bin=/app/mysql/binlogs/bin-log-mysqld2 --log-bin-index=/app/mysql/binlogs/bin-log-mysqld2.index --max_binlog_size=1024M
root 6544 1 0 17:44 pts/0 00:00:00 /bin/sh /app/mysql/bin/mysqld_safe --socket=/app/mysql/run/mysqld.sock3 --port=3308 --pid-file=/app/mysql/run/mysqld.pid3 --datadir=/data/mysqldata3 --lc-messages-dir=/app/mysql/share/english --log-bin=/app/mysql/binlogs/bin-log-mysqld3 --log-bin-index=/app/mysql/binlogs/bin-log-mysqld3.index --max_binlog_size=1024M --socket=/app/mysql/run/mysqld.sock3 --port=3308 --pid-file=/app/mysql/run/mysqld.pid3 --datadir=/data/mysqldata3 --lc-messages-dir=/app/mysql/share/english --log-bin=/app/mysql/binlogs/bin-log-mysqld3 --log-bin-index=/app/mysql/binlogs/bin-log-mysqld3.index --max_binlog_size=1024M
mysql 8326 6528 0 17:44 pts/0 00:00:00 /app/mysql/bin/mysqld --basedir=/app/mysql --datadir=/data/mysqldata --plugin-dir=/app/mysql/lib/plugin --user=mysql --lc-messages-dir=/app/mysql/share/english --lc-messages-dir=/app/mysql/share/english --log-error=/app/mysql/log/mysqld.log --open-files-limit=8192 --pid-file=/app/mysql/run/mysqld.pid --socket=/app/mysql/run/mysqld.sock --port=3306
mysql 8487 6534 0 17:44 pts/0 00:00:00 /app/mysql/bin/mysqld --basedir=/app/mysql --datadir=/data/mysqldata2 --plugin-dir=/app/mysql/lib/plugin --user=mysql --lc-messages-dir=/app/mysql/share/english --log-bin=/app/mysql/binlogs/bin-log-mysqld2 --log-bin-index=/app/mysql/binlogs/bin-log-mysqld2.index --max-binlog-size=1024M --lc-messages-dir=/app/mysql/share/english --log-bin=/app/mysql/binlogs/bin-log-mysqld2 --log-bin-index=/app/mysql/binlogs/bin-log-mysqld2.index --max-binlog-size=1024M --log-error=/app/mysql/log/mysqld.log --open-files-limit=8192 --pid-file=/app/mysql/run/mysqld.pid2 --socket=/app/mysql/run/mysqld.sock2 --port=3307
mysql 8506 6544 0 17:44 pts/0 00:00:00 /app/mysql/bin/mysqld --basedir=/app/mysql --datadir=/data/mysqldata3 --plugin-dir=/app/mysql/lib/plugin --user=mysql --lc-messages-dir=/app/mysql/share/english --log-bin=/app/mysql/binlogs/bin-log-mysqld3 --log-bin-index=/app/mysql/binlogs/bin-log-mysqld3.index --max-binlog-size=1024M --lc-messages-dir=/app/mysql/share/english --log-bin=/app/mysql/binlogs/bin-log-mysqld3 --log-bin-index=/app/mysql/binlogs/bin-log-mysqld3.index --max-binlog-size=1024M --log-error=/app/mysql/log/mysqld.log --open-files-limit=8192 --pid-file=/app/mysql/run/mysqld.pid3 --socket=/app/mysql/run/mysqld.sock3 --port=3308
关闭mysql实例
[root@MySQL ~]# /etc/init.d/mysqld_multid stop 1,2,3
或
[root@MySQL ~]# /etc/init.d/mysqld_multid stop 3306,3307,3308
六、修改实例密码
[root@MySQL ~]# /app/mysql/bin/mysqladmin -uroot -h127.0.0.1 -P3307 password ‘123456‘
[root@MySQL ~]# /app/mysql/bin/mysqladmin -uroot -h127.0.0.1 -P3308 password ‘123456‘
七、登录多实例
[root@MySQL ~]# mysql -uroot -p123456 -h127.0.0.1 -P3306 #登录时只是端口号不一样
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.23 Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql>
八、删除匿名连接的空密码帐号
注,分别登录实例[mysqld1]、[mysqld2]、[mysqld3]执行以下命令:
mysql>use mysql; #选择系统数据库mysql
mysql>select Host,User,Password from user; #查看所有用户
mysql>delete from user where password="";#删除无密码账户
mysql>flush privileges; #刷新权限
mysql>select Host,User,Password from user; #确认密码为空的用户是否已全部删除
mysql>exit;
配置mysql允许root远程登录 #登录
mysql> grant all privileges on *.* to root@‘%‘ identified by "123456";
mysql> flush privileges;
mysql> select User,Password,Host from user;
原文:http://yylinux.blog.51cto.com/8831641/1677678
内容总结
以上是互联网集市为您收集整理的MySQL 5.6对已有Mysql单实例的机器,再添加mysql数据库,实现单机多实例全部内容,希望文章能够帮你解决MySQL 5.6对已有Mysql单实例的机器,再添加mysql数据库,实现单机多实例所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。