mysql单机多实例

数据库 配置 MySQL

初始化数据库

sudo mysql_install_db --basedir=/Applications/XAMPP/xamppfiles --datadir=/Applications/XAMPP/xamppfiles/var/mysql3308

数据库目录授权（_mysql参考原有的mysql目录所有者和分组）

sudo chown -R _mysql:_mysql /Applications/XAMPP/xamppfiles/var/mysql3308

创建tmp目录

sudo mkdir /Applications/XAMPP/xamppfiles/temp/mysql3308
sudo chown -R _mysql:_mysql /Applications/XAMPP/xamppfiles/temp/mysql3308

启动脚本

#!/bin/sh
#@filename /Applications/XAMPP/bin/mysqld_multi.server
#@usage sudo /Applications/XAMPP/bin/mysqld_multi.server start 1,2      #注：1对应[mysqld1]
#
# 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=/Applications/XAMPP/xamppfiles
bindir=/Applications/XAMPP/xamppfiles/bin
cnffile=/Applications/XAMPP/xamppfiles/etc/my.cnf
defaultsfile="--defaults-file=$cnffile"
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" "$defaultsfile" start $2
        ;;
    'stop' )
        "$mysqld_multi" "$defaultsfile" stop $2
        ;;
    'report' )
        "$mysqld_multi" "$defaultsfile" report $2
        ;;
    'restart' )
        "$mysqld_multi" "$defaultsfile" stop $2
        "$mysqld_multi" "$defaultsfile" start $2
        ;;
    *)
        echo "Usage: $0 {start|stop|report|restart}" >&2
        ;;
esac


sudo mysqld_multi --defaults-file=/Applications/XAMPP/xamppfiles/etc/my.cnf start 1

/Applications/XAMPP/xamppfiles/etc/my.cnf

[client]
default-character-set = utf8
[mysqld_multi]
mysqld = /Applications/XAMPP/xamppfiles/bin/mysqld_safe
mysqladmin = /Applications/XAMPP/xamppfiles/bin/mysqladmin
log = /Applications/XAMPP/xamppfiles/logs/mysqld_multi.log
[mysqld6]
user=mysql
port=3306
server-id=3306
socket      =/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock
pid-file = /Applications/XAMPP/xamppfiles/var/mysql/Zjmainstay-PC.pid
datadir = /Applications/XAMPP/xamppfiles/var/mysql
innodb_data_home_dir=/Applications/XAMPP/xamppfiles/var/mysql/
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_group_home_dir=/Applications/XAMPP/xamppfiles/var/mysql/
tmpdir=/Applications/XAMPP/xamppfiles/temp/mysql
[mysqld7]
user=mysql
port=3307
server-id=3307
socket      =/Applications/XAMPP/xamppfiles/var/mysql3307/mysql.sock
pid-file = /Applications/XAMPP/xamppfiles/var/mysql3307/Zjmainstay-PC.pid
datadir = /Applications/XAMPP/xamppfiles/var/mysql3307
innodb_data_home_dir=/Applications/XAMPP/xamppfiles/var/mysql3307/
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_group_home_dir=/Applications/XAMPP/xamppfiles/var/mysql3307/
tmpdir=/Applications/XAMPP/xamppfiles/temp/mysql3307
[mysqld8]
user=mysql
port=3308
server-id=3308
socket      =/Applications/XAMPP/xamppfiles/var/mysql3308/mysql.sock
pid-file = /Applications/XAMPP/xamppfiles/var/mysql3308/Zjmainstay-PC.pid
datadir = /Applications/XAMPP/xamppfiles/var/mysql3308
innodb_data_home_dir=/Applications/XAMPP/xamppfiles/var/mysql3308/
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_group_home_dir=/Applications/XAMPP/xamppfiles/var/mysql3308/
tmpdir=/Applications/XAMPP/xamppfiles/temp/mysql3308
[mysqld]
key_buffer=16M
max_allowed_packet=1M
table_open_cache=64
sort_buffer_size=512K
net_buffer_length=8K
read_buffer_size=256K
read_rnd_buffer_size=512K
myisam_sort_buffer_size=8M
log-bin=mysql-bin
plugin_dir=/Applications/XAMPP/xamppfiles/lib/mysql/plugin/
innodb_buffer_pool_size=16M
innodb_log_file_size=5M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=50
[mysqldump]
max_allowed_packet=16M
[mysql]
[isamchk]
key_buffer=20M
sort_buffer_size=20M
read_buffer=2M
write_buffer=2M
[myisamchk]
key_buffer=20M
sort_buffer_size=20M
read_buffer=2M
write_buffer=2M
[mysqlhotcopy]

主从配置

3307   master
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1' IDENTIFIED BY 'replroot';
       GRANT select ON *.* TO 'readonly'@'127.0.0.1' IDENTIFIED BY 'readonlyroot';             #供slave客户端使用，避免错误写入
    flush privileges;
    FLUSH TABLES WITH READ LOCK;
    mysqldump -uroot -P 3307 -h 127.0.0.1 -R --add-drop-database  --events  --all-databases > ~/Desktop/tmp/3307.full.sql
    SHOW MASTER STATUS;         #需要记录结果，后面使用mysql-bin.000004     406
    UNLOCK TABLES;
3308   slave
    mysql -uroot -P 3308 -h 127.0.0.1 < ~/Desktop/tmp/3307.full.sql
    CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3307, MASTER_USER='repl', MASTER_PASSWORD='replroot', MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=406;
    show slave status;
    start slave;
    show slave status #检查Slave_IO_Running:和Slave_SQL_Running:状态   都为yes则配置完成！