mysql从库配置_【原创】数据库基础之Mysql（2）主从库配置

一 安装

# wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

# yum -y install mysql57-community-release-el7-10.noarch.rpm

# yum -y install mysql-community-server

# systemctl start mysqld.service

# grep "password" /var/log/mysqld.log

2019-01-28T07:35:19.672300Z 1 [Note] A temporary password is generated for root@localhost: %ym?OT&

# mysql -uroot -p%ym?OT&

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '$newpass';

二 主从配置

1 master

# vi /etc/my.cnf

[mysqld]

server-id=1

log-bin=master-bin

log-bin-index=master-bin.index

mysql>create user 'repl'@'%' IDENTIFIED BY 'repl';

mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

# service mysqld restart

mysql> SHOW MASTER STATUS;

+-------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| master-bin.000001 | 77411 | | | |

+-------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

2 slave

# vi /etc/my.cnf

[mysqld]

server-id=2

relay-log-index=slave-relay-bin.index

relay-log=slave-relay-bi

#从库只读访问

read_only=1

# service mysqld restart

mysql> change master to master_host='$master_server',master_port=3306,master_user='repl',master_password='repl',master_log_file='master-bin.000001',master_log_pos=0;

mysql> start slave;

mysql> show slave status;

如果主库开启binlog或者配置从库前主库已经有数据，需要手工同步数据到从库，然后再开启主从同步，同步过程为

1 master

mysql > flush tables with read lock;

mysql > show master status;

get $pos

# mysqldump -uroot -p --all-databases > mysql_dump.sql

mysql > unlock tables;

2 slave

# mysql -uroot -p < mysql_dump.sql

mysql > change master to ... master_log_pos=$pos

mysql > start slave;

mysql > show slave status;

ps：主库最少512m内存，从库最少256m内存；

还有一种双主配置，即两台服务器互为主从，为了避免数据冲突，需要将两者的自增id区分(两台就是一奇一偶)：

server1：

auto_increment_offset = 1

auto_increment_increment = 2

server2：

auto_increment_offset = 2

auto_increment_increment = 2