主节点已经运行了一段时间,且有大量数据时,这时候许多方式不能使用,需要我们不停机,设置并启动slave节点,下面做个实验来人人可以参照做

,

联博统计

www.yecailei.com采用以太坊区块链高度哈希值作为统计数据,联博以太坊统计数据开源、公平、无任何作弊可能性。联博统计免费提供API接口,支持多语言接入。

,
#备份主节点数据库
[root@master ~] mysqldump -A -F --single-transaction --master-data=1 > /data/mariadb_backup_`date +%F_%T`.sql

[root@master ~] ll /data/
total 1496 -rw-r--r-- 1 root  root   478777 Oct 16 22:04 mariadb_backup_sql

#将备份拷贝到slave服务器
[root@master ~] scp /data/mariadb_backup_2020-10-18_16\:08\:08.sql 10.0.0.81:/data/
The authenticity of host '10.0.0.81 (10.0.0.81)' can't be established.
ECDSA key fingerprint is SHA256:pWYlWWDNZAyPanC0foRMoATFOGGBma8XydeNm0U1+CI.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '10.0.0.81' (ECDSA) to the list of known hosts.
root@10.0.0.81's password:
mariadb_backup_2020-10-18_16\:09\:08.sql                                                                                     100%  468KB  45.1MB/s   00:00
#优化主从节点服务器性能
MariaDB [(none)]> SET GLOBAL innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> SET GLOBAL sync_binlog= 0;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     |
+---------------+-------+
1 row in set (0.001 sec)
#将备份还原到从节点:
#安装数据库:
[root@slave1 ~] dnf -y install mariadb-server
#修改设置文件
[root@slave1 ~] vim /etc/my.cnf.d/mariadb-server.cnf
[mysql]
server-id=81
log-bin
read-only

[root@slave1 ~] systemctl restart mariadb.service
设置从节点从备份之后最先复制:
[root@slave1 ~] vim /data/mariadb_backup_2020-10-18_08\:12\:52.sql
CHANGE MASTER TO
  MASTER_HOST='10.0.0.81',
  MASTER_USER='abc',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000002',
  MASTER_LOG_POS=375;

[root@slave1 ~] mysql < /data/mariadb_backup_2020-10-18_12:06\:11.sql

[root@slave1 ~] mysql
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.0.0.8
                   Master_User: abc
                   Master_Port: 3306
                  Connect_Retry: 60
                 Master_Log_File: mariadb-bin.000004
              Read_Master_Log_Pos: 389
                 Relay_Log_File: mariadb-relay-bin.000002
                  Relay_Log_Pos: 557
             Relay_Master_Log_File: mariadb-bin.000002
                Slave_IO_Running: Yes 
               Slave_SQL_Running: Yes 
                 Replicate_Do_DB:
              Replicate_Ignore_DB:
               Replicate_Do_Table:
            Replicate_Ignore_Table:
         Replicate_Wild_Ignore_Table:
                    Last_Errno: 0
                    Last_Error:
                  Skip_Counter: 0
              Exec_Master_Log_Pos: 389
               Relay_Log_Space: 868
               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:
         S   econds_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: 8
                Master_SSL_Crl:
             Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
          Replicate_Do_Domain_Ids:
        Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                    SQL_Delay: 0
             SQL_Remaining_Delay: NULL
           Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_DDL_Groups: 0
      Slave_Non_Transactional_Groups: 0
         Slave_Transactional_Groups: 0
1 row in set (0.000 sec)