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;


[root@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

[root@www ~]# 


[root@www data]# mysql --socket=/data/3307/mysql.sock -p"3307" -uroot -e "start slave;"

[root@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

[root@www data]#