我的 "github" 安裝MySQL伺服器 安裝資料庫 初始化資料庫 啟動資料庫 主伺服器基礎搭建 設定主機名，在當前bash生效 創建資料庫 創建表 查看表結構 插入測試數據 設定主伺服器 修改配置文件 給blog表上鎖 導出主庫中已經有的數據 將數據複製到從節點 從節點導入從主節點複製的數據 ...
我的github
安裝MySQL伺服器
- 安裝資料庫
yum install -y mariadb-server
- 初始化資料庫
mysql_secure_installation #MySql初始化腳本，以下為每一項的翻譯
是否設置root密碼
輸入密碼
確認密碼
是否設置匿名用戶
是否允許root遠程登錄
刪除test資料庫
現在是否生效
添加PATH變數
- 啟動資料庫
systemctl start mariadb
主伺服器基礎搭建
- 設定主機名，在當前bash生效
hostnamectl set-hostname mysql-master
exec bash
- 創建資料庫
MariaDB [(none)]> create database shuaiguoxia ;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shuaiguoxia |
| test |
+--------------------+
5 rows in set (0.00 sec)
- 創建表
use shuaiguoxia;
create table blog (name varchar(20),age int,sex varchar(10));
- 查看表結構
MariaDB [shuaiguoxia]> desc blog;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
- 插入測試數據
insert into blog (name,age,sex) values ("guo",27,"nan");
insert into blog (name,age,sex) values ("wu",33,"nan");
insert into blog (name,age,sex) values ("cai",31,"nv");
insert into blog (name,age,sex) values ("li",19,"nan");
insert into blog (name,age,sex) values ("zhao",18,"nan");
insert into blog (name,age,sex) values ("qian",25,"nv");
設定主伺服器
- 修改配置文件
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=10
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysq.%
- 給blog表上鎖
mysql -u root -p
use shuaiguoxia;
MariaDB [shuaiguoxia]> lock tables blog read;
Query OK, 0 rows affected (0.00 sec)
- 導出主庫中已經有的數據
mysqldump -u root -p -h 127.0.0.1 --database shuaiguoxia > /bak.sql
Enter password:
# 導出時一定要--dabatase指定資料庫
- 將數據複製到從節點
scp /bak.sql 192.168.1.175:/
- 從節點導入從主節點複製的數據
mysql -u root -p < /bak.sql
- 給從節點創建授權用戶
grant replication slave on *.* to 'slave_user'@'192.168.1.%' identified by '1234'
從節點配置
- 設定主機名，在當前bash生效
hostnamectl set-hostname slave-master
exec bash
- 設定配置文件
[root@mysql-slave ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=21
log-bin=mysql-bin
replicate-wild-ignore-table=mysql.%
- 查看主節點的master狀態
# 查看主節點的master狀態，要在主節點執行
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 2900 | | |
+------------------+----------+--------------+------------------+
- 從節點導入從主節點複製的數據
# 前面導入過就不同導入了，怕你忘了再提一句
mysql -u root -p < /bak.sql
- 設定自動複製
# 命令中的master_log_file和master_log_pos為主節點查詢的結果
MariaDB [(none)]> change master to \
master_host='192.168.1.46',
master_user='slave_user',
master_password='1234',
master_log_file='mysql-bin.000003',
master_log_pos=2900;
- 啟動複製
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
- 查看複製狀態
# 正常狀態下Slave_IO_Running和 Slave_SQL_Running都為yes
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.46
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 3057
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 686
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3057
Relay_Log_Space: 982
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: 10
1 row in set (0.00 sec)
主從失敗常見原因
- 網路不通：排查網路、埠
- 用戶密碼不對：檢查在主節點創建的用戶名密碼
- 用戶許可權不對：MySQL中對一個用戶的標識為IP@username，
- pos不對：檢查從節點設定的開始pos是否為主節點正在進行的pos
- 開始二進位文件不對：檢查從節點開始的二進位文件是否為主節點整個在進行的二進位位內按
- 防火牆限制：關閉防火牆
從節點導入數據不生效
- 主節點導出數據時要添加--databases參數
mysqldump -u root -p -h 127.0.0.1 --database shuaiguoxia > /bak.sql
Enter password:
# 導出時一定要--dabatase指定資料庫
- 從節點導入資料庫是要使用mysql命令
mysql -u root -p < /bak.sql
