mysql主从安装_mysql主从复制安装详解

1、环境准备：两台Linux6.8服务器，(可以通过yum安装mysql：yum install mysql mysql-server -y，

安装的版本可能比较低，对于练手来说够用了 )

主库ip：192.168.0.2，从库ip：192.168.0.3

2、主库操作

[root@localhost ~]# vim /etc/my.cnf

[mysqld] #在mysqld下增加server-id和log-bin日志目录

server-id = 1 #主server-id为1，不能重复

log-bin=/var/lib/mysql/mysql-bin #log-bin目录

[root@localhost ~]# service mysqld restart # 重启服务

mysql> show variables like 'log_bin'; # 查看是否打开 binlog 日志

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

| Variable_name | Value |

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

| log_bin | ON |

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

mysql> grant replication slave on *.* to 'rep'@'192.168.0.%' identified by 'rep'; # 建立从库复制账号密码 rep/rep

mysql> flush privileges;

mysql> show master status; # 主库当前日志文件和当前偏移量，下面会用到这两个值

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 | 100 | | |

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

3、从库操作

[root@localhost ~]# vim /etc/my.cnf

server-id = 2 #只添加server-id即可

[root@localhost ~]# service mysqld restart # 重启数据库

#执行下面命令，将相关复制信息导入从库

mysql << EOF

CHANGE MASTER TO

MASTER_HOST='192.168.0.2', # 主库 IP

MASTER_PORT=3306, # 主库端口

MASTER_USER='rep', # 复制用户

MASTER_PASSWORD='rep', # 用户密码

MASTER_LOG_FILE='mysql-bin.000001', # 上面看到的日志文件名

MASTER_LOG_POS=100; # 上面看到的日志偏移量

EOF

[root@localhost ~]# mysql -e "start slave"; # 启动主从复制

[root@localhost ~]# mysql -e "show slave status"; # 查看主从复制是否成功

4、测试

[root@localhost ~]# mysql -e "create database rep"; #主库执行

[root@localhost ~]# mysql -e "show databases like 'rep'"; #从库执行

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

| Database (rep) |

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

| rep |

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