搭建主从步骤

最新推荐文章于 2024-05-20 14:38:13 发布

菜鸟来咯

最新推荐文章于 2024-05-20 14:38:13 发布

阅读量77

点赞数

文章标签： mysql 数据库 java Powered by 金山文档

本文链接：https://blog.csdn.net/weixin_70661795/article/details/129304608

版权

背景：主服务器已经运行一段时间，已有数据存在，先新加入从库，需保证已有数据同步

安装mysql从机，端口号3309

安装路径

MacBook-Pro:mysql_14 root#  cd /use/local/mysql_14/
MacBook-Pro:mysql_14 root#  mkdir 3309
MacBook-Pro:mysql_14 root#  chown  -R mysql:mysql 3309
MacBook-Pro:mysql_14 root#  cp -R /usr/local/mysql_14/3307/mysql-5.7.14-osx10.11-x86_64  /usr/local/mysql_14/3309
MacBook-Pro:mysql_14 root#  chown  -R mysql:mysql mysql-5.7.14-osx10.11-x86_64/
MacBook-Pro:mysql_14 root#  cp -R /etc/my.cnf14  /etc/my.cnf09

更改配置文件
MacBook-Pro:mysql_14 root#  vim /etc/my.cnf09
   [mysqld]
   basedir=/usr/local/mysql_14/3309/mysql-5.7.14-osx10.11-x86_64
   datadir=/usr/local/mysql_14/3309/data_09
   port=3309
   user=mysql
   socket=/tmp/mysql09.sock
   log-error=/usr/local/mysql_14/3309/data_09/mysqld09.err
   server_id = 9
   log_bin = /opt/mylog/db09

创建数据目录
MacBook-Pro:mysql_14 root#  mkdir data_09
MacBook-Pro:mysql_14 root#  chown  -R mysql:mysql data_09
 

创建binlog日志目录
MacBook-Pro:mysql_14 root#  mkdir /opt/mylog07

MacBook-Pro:mysql_14 root# cd /usr/local/mysql_14/3309/mysql-5.7.14-osx10.11-x86_64/

初始化
MacBook-Pro:mysql_14 root#  ./bin/mysqld --defaults-file=/etc/my.cnf09 --initialize --user=mysql
MacBook-Pro:mysql_14 root#  cd ..
MacBook-Pro:mysql_14 root#  more data_09/mysqld09.err 
MacBook-Pro:mysql_14 root#  cd mysql-5.7.14-osx10.11-x86_64/

启动mysql
MacBook-Pro:mysql_14 root#  ./bin/mysqld_safe --defaults-file=/etc/my.cnf09 --user=mysql &

进入mysql并修改密码
MacBook-Pro:mysql_14 root#  mysql -S /tmp/mysql09.sock -uroot -p'w&qr<ce%e7<J'
   mysql> alter user root@'localhost' identified by '123';
   Query OK, 0 rows affected (0.07 sec)
MacBook-Pro:mysql_14 root#  mysql -S /tmp/mysql09.sock -uroot -p'123'


查看mysql端口号3309

mysql> show variables like '%port%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_support_xa        | ON    |
| large_files_support      | ON    |
| port                     | 3309  |
| report_host              |       |
| report_password          |       |
| report_port              | 3309  |
| report_user              |       |
| require_secure_transport | OFF   |
+--------------------------+-------+
8 rows in set (0.01 sec)

查看主服务器端口号 3306

mysql> show variables like "%port%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_support_xa        | ON    |
| large_files_support      | ON    |
| port                     | 3306  |
| report_host              |       |
| report_password          |       |
| report_port              | 3306  |
| report_user              |       |
| require_secure_transport | OFF   |
+--------------------------+-------+
8 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| atguigudb          |
| db_xuanwu          |
| dbtest14           |
| dbtest15           |
| dbtest24           |
| dbtest28           |
| employees          |
| mysql              |
| performance_schema |
| sys                |
| tarena             |
| test               |
| test_mc            |
+--------------------+
14 rows in set (0.02 sec)

主服务器创建replication slave 用户

mysql> grant replication slave on *.* to 'repl'@'127.0.0.1' identified by 'replsafe';
Query OK, 0 rows affected, 1 warning (0.08 sec)

查看master 状态，获取log_file及log_pos

mysql> show master status;
+-------------+----------+--------------+------------------+-------------------+
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------+----------+--------------+------------------+-------------------+
| db14.000006 |     1856 |              |                  |                   |
+-------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

锁定全局

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.08 sec)

mysql> exit
Bye

mysqldump 导出所有数据库
MacBook-Pro:mysql_14 root# mysqldump -S /tmp/mysql14.sock -uroot -p123 --all-databases --master_data=2 --column-statistics=0  > /usr/local/mysql_14/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

解锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

查看从服务器现有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.08 sec)

从服务器change master to  搭建主从服务

mysql> change master to
    -> master_host = '127.0.0.1',
    -> master_port = 3306,
    -> master_user = 'repl',
    -> master_password = 'replsafe',
    -> master_log_file= 'db14.000006',
    -> master_log_pos = 1856;
Query OK, 0 rows affected, 2 warnings (0.65 sec)

开启slave服务

mysql> start slave;
Query OK, 0 rows affected (0.07 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: db14.000006
          Read_Master_Log_Pos: 1856
               Relay_Log_File: MacBook-Pro-relay-bin.000002
                Relay_Log_Pos: 315
        Relay_Master_Log_File: db14.000006
             Slave_IO_Running: Yes                IO线程为YES
            Slave_SQL_Running: Yes                SQL线程为YES
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1856
              Relay_Log_Space: 528
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 14
                  Master_UUID: 74d693ec-956e-11ed-9a04-c7aafbefbbd8
             Master_Info_File: /usr/local/mysql_14/3309/data_09/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

把备份的数据文件导入从服务器

mysql -S /tmp/mysql09.sock -uroot -p123   < /usr/local/mysql_14/all.sql  --default-character-set=utf8mb4
mysql: [Warning] Using a password on the command line interface can be insecure.

查看从服务器现在的数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| atguigudb          |
| db_xuanwu          |
| dbtest14           |
| dbtest15           |
| dbtest24           |
| dbtest28           |
| employees          |
| mysql              |
| performance_schema |
| sys                |
| tarena             |
| test               |
| test_mc            |
+--------------------+
14 rows in set (0.01 sec)

验证主从同步
 主数据
mysql> select * from slave;
+------+------+
| id   | name |
+------+------+
|    1 | lisi |
+------+------+
1 row in set (0.01 sec)


从服务器数据
mysql> select * from slave;
+------+------+
| id   | name |
+------+------+
|    1 | lisi |
+------+------+
1 row in set (0.02 sec)


主服务器插入数据
mysql> insert into slave values (1,'zhsan');
Query OK, 1 row affected (0.06 sec)

mysql> select * from slave;
+------+-------+
| id   | name  |
+------+-------+
|    1 | lisi  |
|    1 | zhsan |
+------+-------+
2 rows in set (0.00 sec)

mysql>


验证从服务器是否同步到
mysql> select * from slave;
+------+-------+
| id   | name  |
+------+-------+
|    1 | lisi  |
|    1 | zhsan |
+------+-------+
2 rows in set (0.00 sec)

mysql>