搭建MySQL服务器，并实现主从复制

拼搏现实的明天。 2022-03-30 07:55 158阅读 0赞

# 环境 #

主库ip：192.168.253.128  
从库ip：192.168.253.140  
系统：CentOS7  
MySQL版本：5.6.39

# 第一步：安装MySQL #

这里使用二进制包方式安装，二进制包可以在官网下载 [https://dev.mysql.com/downloads/mysql/][https_dev.mysql.com_downloads_mysql]

在主库和从库上都安装MySQL

tar -xf mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz
    mv mysql-5.6.39-linux-glibc2.12-x86_64 /usr/local/mysql5.6
    ln -sv /usr/local/mysql5.6 /usr/local/mysql
    PATH=/usr/local/mysql/bin/:$PATH
    useradd -M -s /sbin/nologin  mysql
    chown -R mysql:mysql /usr/local/mysql5.6

初始化数据库

yum install perl-Data-Dumper -y
    yum install libaio libaio-devel -y #不安装这个包的话初始化数据库可能会报错缺包
    /usr/local/mysql/scripts/mysql_install_db  --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/

# 第二步：修改主库配置文件 #

[root@localhost src]# cat /etc/my.cnf
    [mysqld]
    datadir=/usr/local/mysql/data
    socket=/tmp/mysql.sock
    symbolic-links=0
    user=mysql
    innodb_file_per_table=1
    server-id=1
    log-bin=/usr/local/mysql/data/master-log
    
    [mysqld_safe]
    log-error=/usr/local/mysql/data/err.log
    pid-file=/usr/local/mysql/data/mysql.pid
    
    !includedir /etc/my.cnf.d

启动主库

mysqld_safe  &

进入

mysql

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70]  
进行一些简单的安全设置

mysql_secure_installation -uroot

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 1]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 2]

进入数据库添加用户，允许从库连接

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'192.168.253.140' IDENTIFIED BY  'replication';

# 第三步：修改从库配置文件 #

[mysqld]
    datadir=/usr/local/mysql/data
    socket=/tmp/mysql.sock
    symbolic-links=0
    user=mysql
    innodb_file_per_table=1
    server-id=2    #这里的server-id一定要跟主库不同
    relay-log=/usr/local/mysql/data  
    
    [mysqld_safe]
    log-error=/usr/local/mysql/data/err.log 
    pid-file=/usr/local/mysql/data/mysql.pid  
    
    !includedir /etc/my.cnf.d

启动

mysqld_safe  &

进行一些简单的安全设置

mysql_secure_installation -uroot

# 第四步：备份主库 #

mysqldump -uroot -p --flush-logs --quick --single-transaction --master-data=2 --all-databases > ~/alldb.sql

再然后在从库上还原备份的主库

scp root@192.168.253.128:/root/alldb.sql ~/ #把备份文件拉到从库机器上
    mysql -uroot -p < ~/alldb.sql  # 导入备份文件

# 第五步：在从库配置主从 #

首先需要看一下逻辑备份的文件，看一下主库binlog日志的位置，从哪开始往下复制  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 3]  
根据位置设置主从

CHANGE MASTER TO 
    MASTER_HOST='192.168.253.128', 
    MASTER_USER='repl', 
    MASTER_PASSWORD='replication', 
    MASTER_LOG_FILE='master-log.000002', 
    MASTER_LOG_POS=120;

启动

mysql> START SLAVE;

查看是否成功

mysql> SHOW SLAVE STATUS\G

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 4]  
看到下面这两个就意味着成功了。

[https_dev.mysql.com_downloads_mysql]: https://dev.mysql.com/downloads/mysql/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70]: /images/20220330/10264e656b54456ab6482b412964c8b8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 1]: /images/20220330/8e329fcd9e6844f69394057c7166e8f4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 2]: /images/20220330/94b5c71014aa4c439e4178700666cde9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 3]: /images/20220330/dc36768465404748a92aa6a49d327fad.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 4]: /images/20220330/f884f8ac6bc74a40b81821b632b14cff.png