之前在自己的虚拟机上一台服务器安装了两个mysql实例,现在利用这两个实例来尝试配置mysql的主从关系。
将3306端口的实例设为主库,3307端口的实例设为从库。
步骤:
1、修改主库和从库的配置文件my.cnf:
主库:[root@single1 mysql]# cat my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/mysql.sock
port=3306
user=mysql
server-id=1
log-bin=mysql-bin
binlog_ignore_db=mysql
character_set_server=utf8
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_data_file_path=ibdata1:12M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=50331648
[mysqld_safe]
log-error=/usr/local/mysql/single1.log
[mysql]
socket = /usr/local/mysql/mysql.sock
default-character-set=utf8
从库:
[root@single1 mysql3307]# cat my.cnf
[mysqld]
basedir=/usr/local/mysql3307
datadir=/usr/local/mysql3307/data
socket=/usr/local/mysql3307/mysql.sock
port=3307
user=mysql
server-id=2
log-bin=mysql-bin
binlog_ignore_db=mysql
character_set_server=utf8
[mysqld_safe]
log-error=/usr/local/mysql3307/single1.log
[mysql]
socket = /usr/local/mysql3307/mysql.sock
default-character-set=utf8
注意:
主库和从库的server-id是必须不一样的,默认为1。
log-bin参数表示启用二进制日志。
binlog_ignore_db参数表示忽略mysql数据库的日志
2、重启mysql实例:
service mysql restart
service mysql3307 restart
3、创建用户用于同步用:
从库:
mysql -S /usr/local/mysql3307/mysql.sock -P 3307 -uroot -p
mysql> create user backup;
Query OK, 0 rows affected (0.01 sec)
mysql> set password for backup=password('backup');
Query OK, 0 rows affected (0.00 sec)
主库:
mysql -S /usr/local/mysql/mysql.sock -P 3306 -uroot -p
mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* to backup@'192.168.2.151' identified by 'backup';
Query OK, 0 rows affected (0.05 sec)to backup@'192.168.2.151' identified by 'backup';
注:192.168.2.151这个IP地址是从库的IP地址。
REPLICATION CLIENT权限,用于查看slave进程的状态
REPLICATION SLAVE权限,用于复制进程
SUPER权限,用于停止和开启slave进程
RELOAD权限,用于reset slave;
4、查看主库的状态:
主库:
mysql -S /usr/local/mysql/mysql.sock -P 3306 -uroot -p
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)
5、配置从库:
从库:
mysql -S /usr/local/mysql3307/mysql.sock -P 3307 -ubackup -p
change master to master_host='localhost', master_user='backup', master_password='root', master_log_file='mysql-bin.000001', master_log_pos=120, master_port=3306;
#一定要指定相应的端口号,不然在查看状态时会出错。master_log_file和master_log_pos的值由主库的状态查看得出。
start slave;
show slave status\G;
注:Slave_IO及Slave_SQL进程必须正常运行,即YES状态,否则都是错误的状态(如:其中一个NO均属错误)。
6、测试:
主库:
create database test2;
use test2;
create table t as select * from mysql.user;
备库:
show database;
use test2;
select count(1) from t;
到此,mysql主从配置结束
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28497416/viewspace-2124179/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28497416/viewspace-2124179/