MySQL數據管理6

 配置主從同步的步驟

環境準備

從一定要有主庫比自己多個的數據

systemctl   stop  firewalld

setenforce  0

物理連接能通 

配置主數據庫服務器

1  用戶授權  

grant  replication  slave  on   *.*  to   plj@"%"  identified by  "123456";

2 修改配置文件

vim /etc/my.cnf

server_id=12

log_bin=master12

log_format="mixed"

:wq

3 重啟數據服務

systemctl  restart  mysqld

4 查看當前使用的 binlog日志信息

mysql  -uroot   -p123123

mysql> show master status;

配置從數據庫服務器

1  驗證主庫授權用戶

mysql  -h292.168.4.12   -uplj   -p123456

mysql> 

2  修改配置文件

vim /etc/my.cnf

server_id=11

log_bin=slave11   #可選

log_format="mixed"

:wq

3  重啟數據服務

systemctl  restart  mysqld

4  管理員登錄把自己指定為從數據庫服務器

mysql -uroot  -p654321

mysql> show  slave status\G;

mysql> change  master  to  master_host="192.168.4.12",master_user="plj",master_password="123456",master_log_file="master12.000001",master_log_pos=154;

5  啟動slave 進程

mysql> start  slave;

6  查看slave 進程狀態信息

mysql> show  slave status\G;

slave_io_running: yes

slave_sql_running: yes

驗證主從同步配置

客戶端訪問主數據庫服務器，創建庫 表記錄 ，在從數據服務器上也能夠看到

主從同步工作原理

slave_io_running:  把主數據庫服務器上binlog日志里的sql 存放到本機的中繼日志文件里。

Last_IO_Error: 報錯信息

出錯原因： 從服務器連接不上主數據庫服務器

                   ping   firewalld   selinux   

                   change master to 的配置信息錯誤。

slave_sql_running：執行本機中繼日志文件里的sql命令。

Last_SQL_Error: 報錯信息

mysql>stop  slave;

mysql>change  master  to  選項=值，選項=值；

mysql>start  slave;

cd /var/lib/mysql/

master.info   連接主數據庫服務器的信息

relay-log.info  中繼日志文件

localhost-relay-bin.xxxxxx   中繼日志文件

localhost-relay-bin.index     中繼日志索引文件

把一臺數據庫服務器恢復為獨立的數據庫服務器。

#cd /var/lib/mysql

#rm -rf  master.info   relay-log.info    localhost-relay-bin.*

#systemctl  restart   mysqld

讓從數據庫服務器臨時不同步數據(想同步再啟動就可以了)

mysql>stop  slave ;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

day06

mysql主從同步結構模式

一主一從

一主多從

主從從

主主結構（互為主從）

+++++++++++++++++++++++++++++

mysql主從同步配置參數

主數據庫服務器使用的配置參數

/etc/my.cnf

[mysqld]

binlog_do_db=數據庫名,數據庫名    #只允許同步的庫

binlog_ignore_db=數據庫名,數據庫名  #只不允許同步的庫

：wq

#systemctl  restart   mysqld

從數據庫服務器使用的配置參數

/etc/my.cnf

[mysqld]

log_slave_updates # 允許級聯復制

relay_log=名       #中繼日志文件名

replicate_do_db=數據庫名,數據庫名 #只同步的庫

replicate_ignore_db=數據庫名,數據庫名  #只不同步的庫

:wq

#systemctl  restart   mysqld

++++++++++++++++++++++++++++++

中間件maxscale

讀寫分離 （maxscale +  mysql主從同步）

maxscale-2.1.2-1.rhel.7.x86_64.rpm

需求 在主機192.168.4.10配置讀寫分離服務，客戶端連接后，

把執行的查詢請求分配給192.168.4.11主機，執行的寫請求分

配給192.168.4.12主機，

配置

1  把11 主機配置為12的從數據庫服務器

2 在主機10運行實現讀寫分離功能的服務maxscale

2.1 安裝軟件包

rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm

2.2 修改配置文件

# sed -i  '/#/d' /etc/maxscale.cnf

# vim /etc/maxscale.cnf

[root@localhost ~]# cat /etc/maxscale.cnf

[maxscale]

threads=1

[server1]

type=server

address=192.168.4.12

port=3306

protocol=MySQLBackend

[server2]

type=server

address=192.168.4.11

port=3306

protocol=MySQLBackend

[MySQL Monitor]

type=monitor

module=mysqlmon

servers=server1,server2

user=scalemon

passwd=111111

monitor_interval=10000

[Read-Write Service]

type=service

router=readwritesplit

servers=server1,server2

user=maxscale

passwd=111111

max_slave_connections=100%

[MaxAdmin Service]

type=service

router=cli

[Read-Write Listener]

type=listener

service=Read-Write Service

protocol=MySQLClient

port=4006

[MaxAdmin Listener]

type=listener

service=MaxAdmin Service

protocol=maxscaled

socket=default

port=6606

[root@localhost ~]# 

配置文件說明

0 設置服務運行啟動的線程數量 

[maxscale]

1 數據庫服務器成員  [server1]    [server2]

2 監控數據庫服務器成員列表 [MySQL Monitor]

user=scalemon     //用戶名

passwd=111111   //密碼

3 指定在那些數據庫服務器之間做讀寫分離

[Read-Write Service]

user=maxscale     //用戶名

passwd=111111  //密碼

4 定義管理服務       

[MaxAdmin Service]  

5 讀寫分離服務監控的端口號 

[Read-Write Listener]

6 指定管理服務監聽的端口號

[MaxAdmin Listener]

2.2根據配置文件的設置添加授權用戶(12   11)

scalemon用戶

grant replication slave, replication client on *.* to scalemon@"%"  identified by “111111”;  //創建監控用戶

maxscale用戶

grant select on mysql.* to maxscale@'%' identified by "111111”;  //創建路由用戶

2.4 啟動maxscale

#maxscale  -f  /etc/maxscale.cnf

#netstat -utnalp  | grep ：4006

#netstat -utnalp  | grep ：6606

#maxadmin -uadmin  -pmariadb -P6606

MaxScale> list servers

Servers.

-------------------+-----------------+-------+-------------+--------------------

Server             | Address         | Port  | Connections | Status              

-------------------+-----------------+-------+-------------+--------------------

server1            | 192.168.4.12    |  3306 |           0 | Master, Running

server2            | 192.168.4.11    |  3306 |           0 | Slave, Running

-------------------+-----------------+-------+-------------+--------------------

MaxScale>quit

[root@localhost ~]#

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

在主從數據庫服務器上客戶端訪問數據時使用的連接用戶

mysql>grant all on  *.*  to student@"%" identified by 

"123456";

客戶端訪問

#which mysql

#mysql  -h292.168.4.10   -P4006 -ustudent   -p123456

mysql>select   ---->  11   從

mysql>insert   ---->  12   主

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

mysql性能調優

數據庫服務器處理客戶端的訪問速度特別慢，可能是由哪些原因

造成的？

1 網絡帶寬不夠

2 服務器配置太低

3提供數據庫服務軟件版本低

查看服務運行時的運行參數（變量）

mysql  -uroot  -p123123

mysql> show  variables  like  "%關鍵字%";

mysql> set  [GLOBAL]  變量名=值；

vim /etc/my.cnf

[mysqld]

變量=值

:wq

并發連接數

max_connections

mysql> show  global  status like "%max_used_conn%";

Max_used_connections/max_connections=0.85  * 100%

連接超時時間

mysql>show  variables  like   "%timeout%";

connect_timeout=10

wait_timeout=28800 

允許保存在緩存中被重用的線程數量

thread_cache_size=10

為所有線程緩存的打開的表的數量

table_open_cache =3

查詢緩存參數設置？

mysql> show  variables like  "query_cache%";

query_cache_type=0/1/2

1  select * from t1;   <1m

2  select in_sql_cache * from t1;

query_cache_wlock_invalidate  off

對myisam 存儲引擎有效。

pc1   select   name  from t1 where name="jim"; 

          name=jim

pc2  select   name  from t1 where name="jim";

pc3  update  t1  set  name="tom" where name="jim";

顯示查詢緩存統計信息

mysql>show global status like  "qcache%";

4程序員編程程序有問題（訪問數據是編寫的sql命令復雜導致處

理速度慢）

mysql服務支持4種日志文件：

錯誤日志     binlog   查詢日志   慢查詢日志

默認開啟

查詢日志：客戶端連接服務器后執行的所有的sql命令。

general-log

general-log-file=目錄/名

默認存儲位置和名稱

cat  /var/lib/mysql/主機名.log

慢查詢日志 ：客戶端連接服務器后，記錄超過指定時間（默認

10秒）顯示查詢結果的sql命令。

slow-query-log

slow-query-log-file=目錄/名

long-query-time=3

默認存儲位置和名稱

mysql> select sleep(11);

cat   /var/lib/mysql/主機名-slow.log

# mysqldumpslow localhost-slow.log > /tmp/sql.err

4程序員編程程序有問題（訪問數據是編寫的sql命令復雜導致處理速度慢）

啟用慢查詢日志 ，記錄超過指定時間顯示查詢結果的sql命令

5網絡結構有問題