简单的mysql主从
主
1、修改/etc/my.cnf文件
basedir = /usr/local/mysql
datadir = /home/mysql/data
port = 3306
server_id = 1
socket = /tmp/mysql.sock
character-set-server = utf8
log-bin=/home/mysql/data/mysql-bin -------二进制日志
log_bin_index=/home/mysql/data/master-bin.index -----类似二进制日志目录
log-error=/home/mysql/logs/log_err.log
general_log=ON -----全日志
general_log_file=/home/mysql/logs/general_log_file.log
long_query_time=2
slow_query_log=ON -------慢日志
slow_query_log_file=/home/mysql/logs/slow_query_log.log
2、登录数据库并创建同步账号
grant replication slave on *.* to 'backup'@'192.168.1.19' identified by 'backup'; //只给个replication的权限就够了
3、查询主数据库状态,并记下FILE及Position的值
show master status;
******************************************
从
4、修改/etc/my.cnf文件
basedir = /usr/local/mysql
datadir = /home/mysql/data
port = 3306
server_id = 2 -------这里不要和主的一样
socket = /tmp/mysql.sock
character-set-server = utf8
log_bin=/home/mysql/data/log-bin
log_bin_index=/home/mysql/data/log-bin.index
log-error=/home/mysql/logs/log_err.log
general_log=ON
general_log_file=/home/mysql/logs/general_log_file.log
long_query_time=2
relay-log=/home/mysql/data/log-relay-bin
slow_query_log=ON
slow_query_log_file=/home/mysql/logs/slow_query_log.log
配置文件里还有好多的选项要写,这里就只是简单的配置主从,实现功能。
5、重启mysql数据库
6、
mysql>change master to master_host='192.168.1.21',master_user='backup',master_password='backup',master_log_file='mysql-bin.000011',master_log_pos=195;
7、启动同步
mysql>start slave;
8、查看
mysql>show slave status\G;
如果您看到Slave_IO_Running和Slave_SQL_Running均为Yes,则主从复制连接正常。
9、测试
在主库上建库,建表等
主
1、修改/etc/my.cnf文件
basedir = /usr/local/mysql
datadir = /home/mysql/data
port = 3306
server_id = 1
socket = /tmp/mysql.sock
character-set-server = utf8
log-bin=/home/mysql/data/mysql-bin -------二进制日志
log_bin_index=/home/mysql/data/master-bin.index -----类似二进制日志目录
log-error=/home/mysql/logs/log_err.log
general_log=ON -----全日志
general_log_file=/home/mysql/logs/general_log_file.log
long_query_time=2
slow_query_log=ON -------慢日志
slow_query_log_file=/home/mysql/logs/slow_query_log.log
2、登录数据库并创建同步账号
grant replication slave on *.* to 'backup'@'192.168.1.19' identified by 'backup'; //只给个replication的权限就够了
3、查询主数据库状态,并记下FILE及Position的值
show master status;
******************************************
从
4、修改/etc/my.cnf文件
basedir = /usr/local/mysql
datadir = /home/mysql/data
port = 3306
server_id = 2 -------这里不要和主的一样
socket = /tmp/mysql.sock
character-set-server = utf8
log_bin=/home/mysql/data/log-bin
log_bin_index=/home/mysql/data/log-bin.index
log-error=/home/mysql/logs/log_err.log
general_log=ON
general_log_file=/home/mysql/logs/general_log_file.log
long_query_time=2
relay-log=/home/mysql/data/log-relay-bin
slow_query_log=ON
slow_query_log_file=/home/mysql/logs/slow_query_log.log
配置文件里还有好多的选项要写,这里就只是简单的配置主从,实现功能。
5、重启mysql数据库
6、
mysql>change master to master_host='192.168.1.21',master_user='backup',master_password='backup',master_log_file='mysql-bin.000011',master_log_pos=195;
7、启动同步
mysql>start slave;
8、查看
mysql>show slave status\G;
如果您看到Slave_IO_Running和Slave_SQL_Running均为Yes,则主从复制连接正常。
9、测试
在主库上建库,建表等
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26909803/viewspace-1851891/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26909803/viewspace-1851891/