首页 > 数据库 > mysql教程 > mysql主从配置的参数配置与步骤_MySQL

mysql主从配置的参数配置与步骤_MySQL

WBOY
发布: 2016-06-01 13:04:05
原创
1247 人浏览过

主从配置的步骤:
在主库建立要同步的数据库,建立主库的帐号和修改主备库配置

create database web default character set utf8

grant replication slave on *.* to 'repdcssub'@'192.168.191.112' identified by '123456';
grant all privileges on *.* to 'repdcssub'@'192.168.191.112' identified by '123456'
mysql -h192.168.191.113 -urep -p123456

mysqldump --master-data 这样可以在从上还原,

建立同步用户(主从)

grant replication slave on *.* to 'repdcs'@'192.168.191.110' identified by '123456';
grant all privileges on *.* to 'repdcs'@'192.168.191.110' identified by '123456';
FLUSH PRIVILEGES;

mysql> show master status;
FLUSH PRIVILEGES;
从库配置my.cnf
[root@ligangtest u02]# more /etc/my.cnf
[mysqld]
datadir=/u02/ligangdata
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
character-set-server=utf8

symbolic-links=0

lower_case_table_names=1

default-storage-engine=innoDB

#innodb_log_buffer_pool_size=2G
max_connections=300

server-id=2
init_connect='SET NAMES utf8'
log-bin=mysqlbin
master-host=192.168.191.111
master-user=repdcs
master-pass=123456
master-connect-retry=60
replicate-do-db=dcs

[mysqld_safe]
log-error=/u03/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
default-character-set=utf8
#character-set-server=utf8


主库配置my.ini

[root@ligang log]# more /etc/my.cnf
[mysqld]
datadir=/u02/ligangdata
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
character-set-server=utf8

# Disabling symbolic-links is recommended to prevent assorted security risks;
symbolic-links=0

lower_case_table_names=1

default-storage-engine=innoDB

server-id=1
log-bin=mysqlbin
innodb_flush_log_at_trx_commit=1
sync_binlog=1
init_connect='SET NAMES utf8'

#innodb_log_buffer_pool_size=2G
max_connections=300

[mysqld_safe]
log-error=/u03/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
default-character-set=utf8
#character-set-server=utf8

参数配置说明
server-id=n //设置数据库id默认主服务器是1可以随便设置但是如果有多台从服务器则不能重复。
master-host=192.168.191.111 //主服务器的IP地址或者域名
master-port=3306 //主数据库的端口号
master-user=repdcs //同步数据库的用户
master-password=123456 //同步数据库的密码
master-connect-retry=60 //如果从服务器发现主服务器断掉,重新连接的时间差
replicate-do-db=dcs //进行同步的数据库


设置从服务器为readonly
mysql -e "set global read_only=1;"

查看主备库正常与否的命令:
SHOW SLAVE STATUS;

SHOW MASTER STATUS;

相关标签:
来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
最新问题
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板