192.168.68.160、192.168.68.162 互為主從關系
192.168.68.163、192.168.68.164 從庫
安裝資料庫：
yum install mariadb mariadb-server -y
修改my.cnf組態檔：
vim /etc/my.cnf
server-id=1 #每臺資料庫的id不能相同！！
log-bin=mysql-bin
relay-log=relay-log
開啟資料庫：
systemctl enable mariadb #開機自啟
systemctl start mariadb #啟動
在192.168.68.160、192.168.68.162：操作互為主從
mysql 中查看 mysql 二進制日志資訊并授權：
192.168.68.160部署：
MariaDB [(none)]> show master status;
+-----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| mysql-bin.00004 | 245 | | |
+-----------------+----------+--------------+------------------+
MariaDB [(none)]> grant all on *.* to lzg@'%' identified by '123456'; '
MariaDB [(none)]> stop slave;
MariaDB [(none)]> change master to master_host='192.168.68.162',master_user='lzg',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=255;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status \G;
顯示下方為雙Yes則主從配置成功：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
192.168.68.162 部署：
MariaDB [(none)]> show master status;
+-----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| mysql-bin.00003 | 255 | | |
+-----------------+----------+--------------+------------------+
MariaDB [(none)]> grant all on *.* to lzg@'%' identified by '123456'; '
MariaDB [(none)]> stop slave;
MariaDB [(none)]> change master to master_host='192.168.68.100',master_user='lzg',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=245;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status \G;
顯示下方為雙Yes則主從配置成功：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
部署高可用：keepalived
yum install keepalived -y
修改keepalived.conf組態檔：
! Configuration File for keepalived
global_defs {
# Keepalived process identifier
lvs_id haproxy_DH
}
# Script used to check if HAProxy is running
vrrp_script check_mysql {
script "pidof mysqld"
interval 2
weight 20}
# Virtual interface br-OAM
# The priority specifies the order in which the assigned interface to take over in a failover
vrrp_instance mysql {
state BACKUP
interface ens33
virtual_router_id 98
priority 10
# The virtual ip address shared between the two loadbalancers
virtual_ipaddress {
192.168.68.100/24
}
track_script {
check_mysql
}
}
啟動keepalived：
systemctl enable keepalived #開機自啟
systemctl start keepadived #啟動
測驗是否成功啟動VIP：
inet 192.168.68.100/24 scope global secondary ens33
安裝部署從庫： 192.168.68.163、192.168.68.164
yum install mariadb mariadb-server -y
修改 my.cnf 組態檔：
vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
relay-log=relay-log
啟動資料庫：
systemctl enable mariadb #開機自啟
systemctl start mariadb #啟動
在192.168.68.163、192.168.68.164 mysql 授權：
MariaDB [(none)]> stop slave;
MariaDB [(none)]> change master to master_host='192.168.68.100',master_user='lzg',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=245;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status \G;
顯示下方為雙Yes則主從配置成功：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
開啟mysql查詢快取：
方法一：{ 修改組態檔：
vim /etc/my.cnf
[mysqld]
query_cache_type = 1
query_cache_size = 100000
}
修改之后需要重啟mysql才能生效，
方法二：{ Sql命令式修改
mysql> set global query_cache_type=1;
mysql> set global_cache_size=200000;<br>mysql> show variables like '%query_cache%';
+-------------------------------+---------+
| Variable_name | Value |
+-------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 199680 |
| query_cache_type | NO |
| query_cache_wlock_invalidate | OFF |
+-------------------------------+---------+
}
mysql> show variables like '%query_cache%';
+-------------------------------+---------+
| Variable_name | Value |
+-------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 199680 |
| query_cache_type | NO |
| query_cache_wlock_invalidate | OFF |
+-------------------------------+---------+
資料庫慢查詢日志：
修改引數：
# vi /etc/my.cnf
[mysqld]
slow_query_log = 1
long_query_time = 1
# service mysqld restart
臨時配置開啟：
mysql> set global slow_query_log='ON';
設定日志存放目錄：
mysql> set slow_query_log_file='/var/lib/mysql/mysql-slow.log';
設定時間：
mysql> set long_query_time=0.1;
查看具體資訊：
mysql> show variables like '%quer%';
+-------------------------------+---------------------------------+
| Variable_name | Value |
+-------------------------------+---------------------------------+
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| log_queries_not_using_indexes | OFF |
| log_slow_queries | OFF |
| long_query_time | 0.100000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | OFF |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
+-------------+-----------------+---------------------------------+
可以使用mysqldumpslow進行分析對慢查詢日志，或者直接cat等進行查看！！！！
驗證：
mysql> select sleep(10);
之后去存盤日志目錄查看是否生成即可，
修改資料庫字符集：
#查看當前資料庫所支持的字符集：
mysql> show charset;
#查詢當前字符編碼：
mysql> show variables like ‘%char%’;
+---------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------+-----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_dayabase | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+-------------+--------------+----------------------------+
引數說明：
character_set_client：客戶端請求資料的字符集，
character_set_connection：從客戶端接收到資料，然后傳輸的字符集，
character_set_database：默認資料庫的字符集，無論默認資料庫如何改變，都是這個字符集；如果沒有默認資料庫，使character_set_server指定的字符集，此引數無需設定，
character_set_filesystem：把作業系統上檔案名轉化成此字符集，即把character_set_client轉換character_set_filesystem，默認binary即可，
character_set_results：結果集的字符集，
character_set_server：資料庫服務器的默認字符集，
character_set_system：這個值總是utf8，不需要設定，存盤系統元資料的字符集，
單獨修改當前表的字符集：
mysql> alter table 表名 convert to character set utf8；
查看表的字符集：
mysql> show table status from 庫名 like 表名；
查看表中所有列的字符集：
mysql> show full columns from 表名;
設定字符集
設定字符集一般有兩種方法，一種是在創建表的時候設定字符集，另一種是表建成之后修改字符集，
創建庫的時候指定字符集：
mysql> create database 庫名 default character set=字符集；
或者
mysql> create database wg charset utf8;
mysql> create database db2 default character set=utf8；
創建表的時候指定字符集：
mysql> create table 表名(屬性) default character set=字符集;
mysql> create table test1(id int(6),name char(10)) default character set = 'gbk';
修改庫的字符集：
mysql> alter database 庫名 default character set 字符集;
mysql> alter database shiyan default character set gbk;
查看：
mysql> show create database shiyan\G
修改單獨庫的字符集：
mysql> alter database 資料庫名 default character set utf8；
修改表的字符集：
mysql> alter table test1 convert to character set utf8;
查看
mysql> show create table test1\G
修改欄位的字符集：
mysql> alter table test1 modify name char(10) character set gbk;
mysql> show full columns from test1;
永久修改方式，修改組態檔修改：
vim /etc/my.cnf
[mysqld]
default-character-set = utf8
[client]
default-character-set = utf8
[mysql]
default-character-set = utf8
資料庫的原理是：
1、兩個執行緒，一個I/O執行緒，一個SQL執行緒， i/o執行緒去請求主庫 的binlog，并將得到的binlog日志寫到relay log（中繼日志） 檔案中；
2、主庫會生成一個 log dump 執行緒，用來給從庫 i/o執行緒傳binlog；
3、SQL 執行緒，會讀取relay log檔案中的日志，并決議成具體操作，來實作主從的操作一致，而最終資料一致；
資料庫的引擎是啥，有啥區別：
1、MyISAM用一個變數保存了整個表的行數，InnoDB不保存表的具體行數，執行select count(*) from table時需要全表掃描，MylSAM執行上述陳述句時只需要讀出該變數即可，速度很快，
2、MyISAM支持全文索引，Innodb不支持全文索引，查詢效率上MyISAM要高，
3、innodb支持事務，MyISAM不支持事務，
4、如果表中絕大多數都只是讀查詢，可以考慮MyISAM，如果既有讀寫也挺頻繁，請使用InnoDB，系統奔潰后，MyISAM恢復起來更困難，
資料庫延遲是如何產生的：
復制代碼
master可以并發，Slave_SQL_Running執行緒卻不可以，
當主庫的TPS并發較高時，產生的DDL數量超過slave一個sql執行緒所能承受的范圍，那么延時就產生了，當然還有就是可能與slave的大型query陳述句產生了鎖等待，
TPS : 是一臺資料庫服務器每秒處理的事務的個數，
DDL：資料庫定義陳述句，用來創建資料庫中的表、索引、視圖、存盤程序、觸發器等，常用的陳述句關鍵字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME，
因素：
1、從庫的配置往往沒有主庫的配置高
2、主庫支持并發寫入，而5.7之前的版本上從庫只有單執行緒SQL來完成任務，
3、MySQL主從之間的同步，并不是完全的實時同步，而是主庫提交事務之后，從庫才再來執行一遍
4、主庫上的表的某個列沒有索引，然后對這個列進行delete或update操作
5、網路問題，往返時延RTT較大，
主庫宕機：
1、確保所有的relay log全部更新完畢，在每個從庫上執行show processlist
2、更新完畢后，登錄所有從庫查看master.info檔案，對比選擇pos最大的作為新的主庫，
3、然后登錄這個新的主庫，執行stop slave；進入主目錄，洗掉master.Info和relay-log.info配置my.cnf檔案開啟log-bin檔案
4、創建用于同步的用戶并授權slave
5、登錄另外一臺從庫，執行stop slave停止同步
6、執行start slave
7、修改新的master資料，測驗slave是否同步更新
從庫宕機：
1、查看從庫上mysql的錯誤日志，里面有記錄主從掛掉時的binlog資訊，
2、有了binlog和postion資訊后，只需要重新在從庫上進行change master to配置即可，配置后開啟slave狀態，沒有報錯
3、查看slave狀態，發現slave已經正常了，開始進行延時資料恢復，
資料庫如何調優：
內核優化：根據硬體配置來進行優化 比如記憶體使用、TCP處理這方面的優化
配置引數的優化：我做過IO處理的常用引數、最大連接數設定、快取使用引數的設定、慢日志的引數的設定
慢查詢日志：
配置慢查詢功能的方式有兩種，一種是使用mysql的組態檔配置,另外一種是使用mysql命令配置，
組態檔配置：
找到[mysqld],在其下面添加如下代碼即可.
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/var/localhost-slow.log
long_query_time=0 log-queries-not-using-indexes = 1
配置好后，重啟mysql服務
使用命令配置：
這里就簡單些一個配置項就行了，其他的配置項均按照此方法配置
mysql> set slow_query_log=ON;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/258488.html
標籤：其他
下一篇：SQL練習52：獲取Employees中的first_name，查詢按照first_name最后兩個字母，按照升序進行排列