Mysql主从搭建文档

环境:一台master 192.168.0.186 (centos 6.4)

一台slave 192.168.0.187

两台都安装mysql源码 yum 都可以,我这里为源码 5.6

 

Selinux iptables 就不说了直接关了吧。直奔主题

 

Master要做什么设置呢

  1. 开启binlog日志

  2. ServerID 别和slave的一样

  3. slave的访问权限(mysql里面的grant命令)

 

Slave要做什么设置嗯

  1. serverid 不一样

change master to 5个选项

master_host='',        master的主机IP

master_user='',                 同步的账号,这里的账号名要和在主grant权限的账号一致;

master_password='',    也是和在主上grant授权的密码一致

master_log_file='’       主上锁表后showmaster status 里面的binlog日志

master_log_pos=;             主上锁表后showmaster status 里面的pos 节点

 

===================================================================

我们先说master,在说slave

这是我的mastermy.cnf文件  5.6的。有的可能和其他版本不兼容,里面有优化参数也可以去掉

[client]

port = 3306

socket = /tmp/mysql.sock

 

 

[mysqld]

user = mysql

port = 3306

socket = /tmp/mysql.sock

datadir = /data/mydata/data

 

#old_passwords = 1

lower_case_table_names = 1

character-set-server = utf8

default-storage-engine = MYISAM

 

server_id = 3                               //ID

log-bin =mysqlmaster-bin.log                 //binlog日志开启和名字

sync_binlog=1

innodb_buffer_pool_size=512M

innodb_flush_log_at_trx_commit=1

lower_case_table_names=1

log_bin_trust_function_creators=1

 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 

log-error = error.log

#long_query_time = 1

#slow_query_log

#slow_query_log_file = slow.log

#binlog_cache_size = 4M

#binlog_format = mixed

#max_binlog_cache_size = 32M

#max_binlog_size = 1G

expire_logs_days = 30

ft_min_word_len = 4

 

back_log = 512

max_allowed_packet = 64M

max_connections = 1000

max_connect_errors = 100

 

join_buffer_size = 8M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

sort_buffer_size = 8M

 

table_open_cache = 1000

thread_cache_size = 256

max_heap_table_size = 64M

tmp_table_size = 64M

thread_stack = 192K

thread_concurrency = 8

 

local-infile = 0

skip-show-database

skip-name-resolve

skip-external-locking

 

connect_timeout = 600

interactive_timeout = 600

wait_timeout = 600

 

#*** MyISAM

key_buffer_size = 512M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 1G

myisam_repair_threads = 1

concurrent_insert = 2

myisam_recover

 

max_allowed_packet = 32M

 

max_heap_table_size = 128M

sort_buffer_size = 16M

join_buffer_size = 16M

thread_cache_size = 16

thread_concurrency = 8

query_cache_size = 128M

ft_min_word_len = 8

thread_stack = 512K

transaction_isolation = REPEATABLE-READ

tmp_table_size = 128M

 

[client]

default-character-set=utf8

 

 

进入mysql授权slave有复制master的权限。

GRANT REPLICATION SLAVEON *.* TO 'repl'@'192.168.0.187' IDENTIFIED BY '123456';

这里我的用户是repl  授权所有库   地址是 187  连接密码是 123456(大小写无所谓)

再刷新权限 flush privileges;

先对mysql上只读锁:FLUSH TABLES WITH READLOCK;

然后就是showmaster status

wKiom1YqBRazEqBfAADPgqQWEt8601.jpg

2个红色方框 slave 配置的时候要用。

Master 就配置完了

 

 

 

 

再来配置slave,先开slave my.cnf

 

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

user = mysql

server_id = 2

port = 3306

socket = /tmp/mysql.sock

datadir = /data/mydata/data

 

#old_passwords = 1

lower_case_table_names = 1

character-set-server = utf8

default-storage-engine = MYISAM

 

#log-bin = mysqlslave-bin.log

sync_binlog=1

 

innodb_buffer_pool_size=512M

innodb_flush_log_at_trx_commit=1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

lower_case_table_names=1

log_bin_trust_function_creators=1

 

 

 

 

log-error = error.log

#long_query_time = 1

#slow_query_log

#slow_query_log_file = slow.log

#binlog_cache_size = 4M

#binlog_format = mixed

#max_binlog_cache_size = 32M

#max_binlog_size = 1G

expire_logs_days = 30

ft_min_word_len = 4

 

back_log = 512

max_allowed_packet = 64M

max_connections = 1000

max_connect_errors = 100

 

join_buffer_size = 8M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

sort_buffer_size = 8M

 

table_open_cache = 1000

thread_cache_size = 256

max_heap_table_size = 64M

tmp_table_size = 64M

thread_stack = 192K

thread_concurrency = 8

 

local-infile = 0

skip-show-database

skip-name-resolve

skip-external-locking

 

connect_timeout = 600

interactive_timeout = 600

wait_timeout = 600

 

#*** MyISAM

key_buffer_size = 512M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 1G

myisam_repair_threads = 1

concurrent_insert = 2

myisam_recover

 

max_allowed_packet = 32M

 

max_heap_table_size = 128M

sort_buffer_size = 16M

join_buffer_size = 16M

thread_cache_size = 16

thread_concurrency = 8

query_cache_size = 128M

ft_min_word_len = 8

thread_stack = 512K

transaction_isolation = REPEATABLE-READ

tmp_table_size = 128M

 

[client]

default-character-set=utf8

 

然后在 changemaster to

 

mysql> change masterto

-> master_host='192.168.0.187',master_user='repl',master_password='123456',master_log_file='mysqlslave-bin.000011',master_log_pos=120;

 

 

然后在启动slave

Start slave

 

在使用 show slave status\G来查看主从是否成功;2yes一个0就可以了。注意pos点,即为同步节点。

         wKiom1YqBUywSRGPAAODkToDNyo831.jpg

然后自己在mastermysql 主机上解锁表:mysql> UNLOCKTABLES;

 

============================验证===========

 

Master 创建一个表会同步到slave上。这就不多说了。

 

==================================可能遇到的错误==========================

 

可以在留言发出来,博主看到会回复哦!哈哈