MySQL多实例

2211we

MySQL多实例
mkdir /data
mkdir /data/3306
mkdir /data/3307
mkdir /data/3307/data
mkdir /data/3306/data

# vim /data/3306/my.cnf
[client]
port = 3306
socket = /data/3306/mysql.sock

[mysql]

[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /opt/mysql/
datadir = /data/3306/data
pid-file = /data/3306/mysql.pid
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
server-id = 3306

[mysqld_safe]
log-error=/data/3306/mysql_3306.err
pid-file = /data/3306/mysql.pid

# vim /data/3307/my.cnf
[client]
port = 3307
socket = /data/3307/mysql.sock

[mysql]

[mysqld]
user = mysql
port = 3307
socket = /data/3307/mysql.sock
basedir = /opt/mysql/
datadir = /data/3307/data
pid-file = /data/3307/mysql.pid
relay-log = /data/3307/relay-bin
relay-log-info-file = /data/3307/relay-log.info
server-id = 3307

[mysqld_safe]
log-error=/data/3307/mysql_3307.err
pid-file = /data/3307/mysql.pid

初始化数据库：
/opt/mysql/scripts/mysql_install_db --basedir=/opt/mysql --datadir=/data/3306/data/ --user=mysql
/opt/mysql/scripts/mysql_install_db --basedir=/opt/mysql --datadir=/data/3307/data/ --user=mysql

chown mysql. /data/* -R

启动数据库：
/opt/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &
/opt/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf &
netstat -tnulp
ps -ef | grep mysql

连接mysql
mysql --socket=/data/3306/mysql.sock
mysql --socket=/data/3307/mysql.sock

关闭数据库
mysqladmin --socket=/data/3306/mysql.sock shutdown
mysqladmin --socket=/data/3307/mysql.sock shutdown

设置密码：
# mysqladmin --socket=/data/3307/mysql.sock  -u root password "3307"
# mysqladmin --socket=/data/3306/mysql.sock  -u root password "3306"

主从复制：
master：
server-id = 3306
log-bin = /data/3306/mysql3306-bin

slave：
server-id = 3307

重启数据库：
# mysqladmin --socket=/data/3307/mysql.sock  -u root -p"3307" shutdown
# mysqladmin --socket=/data/3306/mysql.sock  -u root -p"3306" shutdown
# /opt/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &
# /opt/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf &

登录数据库检查：
# mysql --socket=/data/3306/mysql.sock -p"3306"
mysql> show variables like 'server_id';
mysql> show variables like 'log_bin';
mysql> grant replication slave on *.* to 'rep'@'%' identified by "rep";
mysql> flush privileges;


mysql> flush tables with read lock;

# mysqldump -uroot -p"3306" --socket=/data/3306/mysql.sock --events -A -B |gzip >/server/backup/mysql_3306_bak.$(date +%F).sql.gz
# mysqldump -uroot -p"3306" --socket=/data/3306/mysql.sock |gzip >/server/backup/mysql_3306_bak.$(date +%F).sql.gz

mysql> flush unlock;

[iyunv@www ~]# mysql --socket=/data/3307/mysql.sock -p"3307" -uroot << EOF
> change master to
> master_host='192.168.1.212',
> master_port=3306,
> master_user='rep',
> master_password='rep',
> master_log_file='mysql3306-bin.000001',
> master_log_pos=471;
> EOF
[iyunv@www ~]#

[iyunv@www data]# mysql --socket=/data/3307/mysql.sock -p"3307" -uroot -e "start slave;"
[iyunv@www data]# mysql --socket=/data/3307/mysql.sock -p"3307" -uroot -e "show slave status\G;"

# mysql --socket=/data/3307/mysql.sock -p"3307" -uroot -e "show slave status\G;"|egrep  "Slave_IO_Running|Slave_SQL_Running"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
[iyunv@www data]#