程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql主從配置的參數配置與步驟

mysql主從配置的參數配置與步驟

編輯:MySQL綜合教程

mysql主從配置的參數配置與步驟


主從配置的步驟:
在主庫建立要同步的數據庫,建立主庫的帳號和修改主備庫配置

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;

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved