1、MySQL的多實例： 多實例的特點：能夠有效地利用伺服器的資源，節約伺服器的資源 MySQL多實例的配置有兩種，第一是使用一個配置文件，這種方法不推薦使用，容易出錯；第二種是用多個配置文件，這種方法的好處是可以方便管理。 [root@localhost 3308]# tree -L 2 /dat... ...
1、MySQL的多實例：
多實例的特點：能夠有效地利用伺服器的資源，節約伺服器的資源
MySQL多實例的配置有兩種，第一是使用一個配置文件，這種方法不推薦使用，容易出錯；第二種是用多個配置文件，這種方法的好處是可以方便管理。
[root@localhost 3308]# tree -L 2 /data/
/data/
├── 3307
│ ├── data
│ ├── my.cnf
│ ├── mysql
│ ├── mysql_3307.err
│ ├── mysqld.pid
│ └── mysql.sock
└── 3308
├── data
├── my.cnf
├── mysql
├── mysql_3308.err
├── mysqld.pid
└── mysql.sock
安裝mysql
通過壓縮包哦安裝，提前下載mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz，解壓即可。
添加mysql用戶，
useradd -s /sbin/nologin mysql
將解壓的文件移動到/usr/local並做軟連接
mv mysql-5.5.62-linux-glibc2.12-x86_64 /usr/local
ln -s mysql-5.5.62-linux-glibc2.12-x86_64 /usr/local/mysql
創建每個服務埠的data文件夾
mkdir -p /data/{3307,3308}
進行mysql初始化：
./scripts/mysql_install_db --user=mysql --basedir=/application/mysql/ --datadir=/data/3307/data
./scripts/mysql_install_db --user=mysql --basedir=/application/mysql/ --datadir=/data/3308/data
配置埠的配置文件my.cnf
vim /data/3307/my.cnf
vim /data/3308/my.cnf
[3307/my.cnf]
[client]
port = 3307
socket = /data/3307/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
port = 3307
socket = /data/3307/mysql.sock
basedir = /application/mysql
datadir = /data/3307/data
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#default_table_type = InnoDB
thread_stack = 192K
#transaction_isolation = READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
#long_query_time = 1
#log_long_format
#log-error = /data/3307/error.log
#log-slow-queries = /data/3307/slow.log
pid-file = /data/3307/mysql.pid
#log-bin = /data/3307/mysql-bin
relay-log = /data/3307/relay-bin
relay-log-info-file = /data/3307/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
#myisam_sort_buffer_size = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql
server-id = 8
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 2M
[mysqld_safe]
log-error=/data/3307/mysql_3307.err
pid-file=/data/3307/mysqld.pid
3308的配置文件把3307改成3308
配置各埠的啟動文件
vim /data/3307/mysql
vim /data/3308/mysql
[3307/mysql]
#!/bin/bash
# chkconfig: 2345 21 60
# description: msyql start scripts
port=3307
user=root
passwd=123456
path=/application/mysql/bin
#socket=/tmp/mysql.sock
mysqlsock="/data/${port}/mysql.sock"
function_start(){
if [ -e $mysqlsock ];then
echo "mysql already running..."
else
$path/mysqld_safe --defaults-file=/data/${port}/my.cnf &>/dev/null &
[ $? -eq 0 ]&&{
# . /etc/init.d/functions
echo "mysql start success!!!"
}
fi
}
function_stop(){
if [ -e $mysqlsock ];then
$path/mysqladmin -u$user -p$passwd -S $mysqlsock shutdown &>/dev/null &
[ $? -eq 0 ]&& {
# . /etc/init.d/functions
echo "mysql stop success!!!"
}||echo "mysql stop failed"
else
echo "mysql dont start"
fi
}
function_restart(){
if [ -e $socket ];then
function_stop
sleep 2
function_start
else
function_start
fi
}
function_status(){
[ -e $msyqlsock ]&& echo "MySQL IS RUNNING" || echo "MySQL IS DOWN"
}
case $1 in
start)
function_start
;;
stop)
function_stop
;;
status)
function_status
;;
restart)
function_restart
;;
*)
echo "USAGE |$0{start|stop|status|restart}"
esac
3308的啟動文件將port改成3308
將/data多實例文件添加許可權
chown -R mysql.mysql /data/
啟動資料庫
[root@localhost 3307]# mysqld_safe --defaults-file=/data/3307/my.cnf
/data/3307/mysql start
進入資料庫
[root@localhost ~]# mysql -S /data/3307/mysql.sock
[root@localhost ~]# netstat -lntup
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 18787/mysqld
tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 19506/mysqld
2、MySQL的主從複製
MySQL資料庫的主從複製方案，與使用scp/rsync等命令進行的文件級別複製類似，都是數據的遠程傳輸，只不過MySQL的主從複製是其自帶的功能，無需藉助第三方工具，而且，MySQL的主從複製並不是資料庫磁碟上的文件直接拷貝，而是通過邏輯的binlog日誌複製到要同步的伺服器本地，然後由本地的線程讀取日誌裡面的SQL語句，重新應用到MySQL資料庫中。
主從複製原理
1）在Slave伺服器上執行start slave命令開啟主從複製開關，開始進行主從複製
2）此時，Slave伺服器的I/O線程會通過在Master上已經授權的複製用戶許可權請求連接Master伺服器，並請求從指定binlog日誌文件的指定位置（日誌文件名和位置就是在配置主從複製服務時執行change master命令指定的）之後開始發送binlog日誌內容。
3）Master伺服器接收到來自Slave伺服器的I/O線程的請求後，其上負責複製的I/O線程會根據Slave伺服器的I/O線程請求的信息分批讀取指定binlog日誌文件指定位置之後的binlog日誌信息，然後返回給Slave端的I/O線程。返回的信息中除了binlog日誌內容外，還有在Master伺服器端記錄的新的binlog文件名稱，以及在新的binlog中的下一個指定更新位置。
4）當Slave伺服器的I/O線程獲取到Master伺服器上I/O線程發送的日誌內容，日誌文件及位置點後，會將binlog日誌內容依次寫到Slave端自身的Relay Log（即中繼日誌）文件（MySQL-relay-bin.xxxx）的最末端，並將新的binlog文件名和位置記錄到master-info文件中，以便下一次讀取Master端新binlog日誌時能夠告訴Master伺服器從新binlog日誌的指定文件及位置開始請求新的binlog日誌內容。
5）Slave伺服器端的SQL線程會實時檢測本地Relay Log中I/O線程新增加的日誌內容，然後及時地把Relay Log文件中的內容解析成SQL語句，併在自身Slave伺服器上按解析SQL語句的位置順序執行應用這些SQL語句，併在relay-log.info中記錄當前應用中繼日誌的文件名及位置點。
開啟主資料庫的log-bin：
#在my.cnf文件里的[mysqld]下編輯：
log-bin = /data/3306/mysql-bin
測試log-bin是否開啟：
[root@localhost 3307]# mysql -u root -p -S /data/3306/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.62 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 5 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
建立用於從庫複製的賬號yunjisuan
mysql> grant replication slave on *.* to 'yunjisuan'@'10.6.29.154' identified by 'yunjisuan123';
Query OK, 0 rows affected (0.01 sec)
刷新許可權
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+-----------+-----------------------+
| user | host |
+-----------+-----------------------+
| root | 127.0.0.1 |
| yunjisuan | 10.6.29.154 |
| root | ::1 |
| | localhost |
| root | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+-----------+-----------------------+
7 rows in set (0.00 sec)
備份主表
[root@localhost ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock --events -A -B | gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
查看從資料庫的serverid
[root@localhost ~]# egrep "server-id|log-bin" /data/3307/my.cnf
#log-bin = /data/3307/mysql-bin
server-id = 2
查看從數據的狀態
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.00 sec)
解壓主資料庫備份文件
[root@localhost backup]# gzip -d mysql_bak.2019-09-11.sql.gz
把數據還原到3307
[root@localhost backup]# mysql -u root -p123456 -S /data/3307/mysql.sock <mysql_bak.2019-09-11.sql
登錄從庫，配置複製參數
mysql> show master status
-> ;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='10.6.29.154',MASTER_PORT=3306,MASTER_USER='yunjisuan',MASTER_PASSWORD='yunjisuan123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=345;
Query OK, 0 rows affected (0.01 sec)
開啟主從同步開關，並查看
[root@localhost backup]# mysql -uroot -p123456 -S /data/3307/mysql.sock -e "start slave"
[root@localhost backup]# mysql -uroot -p123456 -S /data/3307/mysql.sock -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.6.29.154
Master_User: yunjisuan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 107
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 107
Relay_Log_Space: 403
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: 5
1 row in set (0.00 sec)
隨後在主庫創建資料庫等，再到從庫查看：
[root@localhost backup]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "create database admin1;"
[root@localhost backup]# mysql -uroot -p123456 -S /data/3307/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| admin1 |
| mysql |
| performance_schema |
| test |
+--------------------+
這樣就完成了mysql資料庫的主從同步。
