如果您要在Production環境作業, 建議是備份完整資料庫, 快的話用innobackupex或是mysqldump將整個資料庫匯出
這次試驗採用vmware產生2 CPU, 1GB RAM, 與充分的磁碟空間, 作業系統採用Debian 9.3的虛擬機, 將我的資料庫以innobackupex copy back到新的Percona Server 5.5
Percona Server 5.5升級5.6版
一定要停止伺服器才能將舊資料copy back, 我的Production環境Percona Server與OS都舊於測試機, 比較好的做法是找跟原本環境同版OS與Percona Server
service mysql stop
在/etc/mysql/conf.d/my.cnf加入過往5.5常用慣例設定
[mysqld]
binlog_format = ROW
character_set_server = utf8
collation_server = utf8_unicode_ci
datadir = /var/lib/mysql
default_storage_engine = InnoDB
expire_logs_days = 7
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_instances = 2
innodb_buffer_pool_size = 819M
innodb_data_file_path = ibdata1:64M;ibdata2:64M:autoextend
innodb_file_format = Barracuda
innodb_file_per_table = true
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 400
innodb_log_file_size = 64M
innodb_print_all_deadlocks = 1
innodb_support_xa = false
key_buffer_size = 32M
log-bin = mysqld-bin
long_query_time = 1
max_allowed_packet = 64M
max_connect_errors = 4294967295
max_connections = 4096
port = 3306
server-id = 123
skip_name_resolve
slow_query_log = 0
thread_cache = 1024
tmpdir = /tmp
transaction_isolation = REPEATABLE-READ
user = mysql
wait_timeout = 60
照老方法將新建好的Percona Server 5.5 /var/lib/mysql目錄下全部清空, 執行innobackupex匯入資料庫
cd /var/lib/mysql
rm -rf *
innobackupex --copy-back /path/to/backup
重新啟動Percona Server 5.5, 確認伺服器有跑起來
service mysql start
可以mysql client接資料庫確認一下
mysql -u root -p
接著關閉5.5, 來升級5.6
service mysql stop
apt-get install percona-server-server-5.6
升到5.6時我遇上卡著/etc/init.d/mysql start, 找出/var/lib/mysql/server_name.err錯誤訊息log檔才發現無法配置記憶體, 是innodb_buffer_pool_size = 819M這個設定過大, 一般來說主機80%的記憶體來當buffer pool不為過, 還是先將低到70%
參考 https://www.percona.com/blog/2013/10/08/a-closer-look-at-percona-server-5-6/ 與 https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/ 修改my.cnf
修改/etc/mysql/conf.d/my.cnf
[mysqld]
binlog_format = ROW
back_log = 2000
character_set_server = utf8
collation_server = utf8_unicode_ci
connect_timeout = 15
datadir = /var/lib/mysql
default_storage_engine = InnoDB
expire_logs_days = 7
innodb_adaptive_hash_index_partitions = 64
innodb_buffer_pool_instances = 2
innodb_buffer_pool_size = 716M
innodb_change_buffering = all
innodb_checksum_algorithm = crc32
innodb_data_file_path = ibdata1:64M;ibdata2:64M:autoextend
innodb_doublewrite = 1
innodb_file_format = Barracuda
innodb_file_per_table = true
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0 # 1 for HDD, 0 for SSD
innodb_io_capacity = 400
innodb_io_capacity_max = 1200
innodb_log_block_size = 512
innodb_log_buffer_size = 64M
innodb_log_file_size = 64M
innodb_log_files_in_group = 2
innodb_lru_scan_depth = 4000
innodb_print_all_deadlocks = 1
innodb_purge_threads = 4
innodb_read_io_threads = 8
innodb_sched_priority_cleaner = 39
innodb_support_xa = false
innodb_write_io_threads = 8
key_buffer_size = 32M
log-bin = mysqld-bin
long_query_time = 1
loose-innodb_sync_array_size = 16
loose-metadata_locks_hash_instances = 256
loose-performance_schema = 0
max_allowed_packet = 64M
max_connect_errors = 4294967295
max_connections = 4096
max_prepared_stmt_count = 1048560
open_files_limit = 15000
query_cache_type = OFF
server-id = 123
skip_name_resolve
sql_mode =
slow_query_log = 0
sync_binlog = 0
table_open_cache = 15000
thread_cache_size = 1024
tmpdir = /tmp
transaction_isolation = REPEATABLE-READ
user = mysql
wait_timeout = 60
必要的話砍掉卡住的/etc/init.d/mysql start, 重新啟動Percona Server 5.6
sql_mode在5.6預設值是NO_ENGINE_SUBSTITUTION, 5.5是空的, 這個看個人需求
Percona Server 5.6升級5.7版
確認5.6有跑起來, 一樣老樣子看error log錯誤訊息來修正my.cnf設定與mysql client去確認資料無誤
停止Percona Server 5.6
service mysql stop
升級5.7
apt-get install percona-server-server-5.7
在升級過程會因my.cnf的設定值錯誤而無法成功啟動Percona Server 5.7, 檢視/var/log/mysql/error.log即可知道哪些設定值衝突到(錯誤訊息不再記到/var/lib/mysql/server_name.err, 5.7有新的設定)
以下是5.6採用而5.7不用的設定, 直接在error.log生錯誤, 整個啟動程序abort...
innodb_adaptive_hash_index_partitions
innodb_log_block_size
innodb_sched_priority_cleaner
另外我也加上預設時區設定到/etc/mysql/conf.d/my.cnf
default_time_zone = SYSTEM
移除上面相關的設定即可, 啟動Percona Server 5.7就行了
service mysql start
接著要手動跑mysql_upgrade(5.6時還會自動做升級)
mysql_upgrade -u root -p
重新啟動Percona Server 5.7來完成升級
service mysql restart
不過在error.log看到警告訊息:
2018-03-13T06:13:43.745826Z 0 [Warning] Could not increase number of max_open_files to more than 1024 (request: 34107)
2018-03-13T06:13:43.745932Z 0 [Warning] Changed limits: max_connections: 214 (requested 4096)
2018-03-13T06:13:43.745936Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 15000)
這個找了StackOverflow有解決辦法: https://stackoverflow.com/questions/30901041/can-not-increase-max-open-files-for-mysql-max-connections-in-ubuntu-15
cp /lib/systemd/system/mysql.service /etc/systemd/system/
nano /etc/systemd/system/mysql.service
貼入
LimitNOFILE=infinity
LimitMEMLOCK=infinity
重啟systemd
systemctl daemon-reload
再次啟動就沒有錯誤訊息了
Percona Server 5.7另外設置了新的設定檔位置/etc/mysql/percona-server.conf.d/, 會影響原本/etc/mysql/conf.d/裡的設定
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp
log-error = /var/log/mysql/error.log
# Recommended in standard MySQL setup
sql_mode=STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION
# Disabling symbolic-links is recommended to prevent assorted security
riskssymbolic-links=0
sql_mode設定影響甚鉅, 我是先全部幹掉(跟5.5一樣是空的)
參考文件: https://www.percona.com/doc/percona-server/5.6/upgrading_guide_55_56.html
https://www.percona.com/doc/percona-server/LATEST/upgrading_guide_56_57.html
