Linux之Mysql升级

升级步骤

1.在opt在创建mysql目录，在mysql目录下再创建data，log，undo，tmp文件夹，为mysql文件改为mysql用户所有：chown -R mysql:mysql mysql/
2.将原数据库安装目录下的data文件夹复制到新建的mysql下的data直接用cp -rf 可以覆盖原文件，将复制过来的data也改为mysql用户所有：chown -R mysql:mysql data/
3.将etc下原有的my.cnf改为my.cnf.bak作为备份，并把新的配置文件改为my.cnf放置到etc目录下，这里我是从本地上传
4.将解压后的安装包复制到/usr/local下，并连接到mysql：ln -s mariadb-10.1.38-linux-x86_64 mysql
5.启动mysql：
cd usr/local/mysql/
bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
6.执行升级：mysql_upgrade -uroot -pawifi@123
7.将/usr/local/mysql/support-files 下的mysql.server复制到/etc/init.d下，改名为mariadb，这样service就能通过关键词mariadb控制msyql了。

参考文档

https://blog.csdn.net/qq_26496673/article/details/82810727
https://zhuanlan.zhihu.com/p/34566414

遇见问题

: Error: 
Table "mysql"."innodb_table_stats"
 not found. InnoDB: Recalculation 
of persistent statistics requested for table "mydatabase"."mytable" 
but the required persistent statistics storage is not present or is corrupted. 
Using transient stats instead.

解决方案：https://stackoverflow.com/questions/15767652/mysql-error-table-mysql-innodb-table-stats-not-found

rm -f innodb_index_stats.frm
rm -f innodb_index_stats.ibd
rm -f innodb_table_stats.frm
rm -f innodb_table_stats.ibd
rm -f slave_master_info.frm
rm -f slave_master_info.ibd
rm -f slave_relay_log_info.frm
rm -f slave_relay_log_info.ibd
rm -f slave_worker_info.frm
rm -f slave_worker_info.ibd

另外执行升级后，mysql的系统表升级成功了，但是自建的没有成功，有待解决

配置文件my.cnf

注意：ibdata1:400M:autoextend的数值大小要根据data里ibdata1的文件大小来写，需要一致

# The following options will be passed to all MySQL clients
[client]
port=3306
socket=/opt/mysql/tmp/mysql.sock

# The MySQL server
[mysqld]
user=mysql
port=3306
socket=/opt/mysql/tmp/mysql.sock
pid-file=Storage.pid
basedir=/usr/local/mysql
datadir=/opt/mysql/data
tmpdir=/opt/mysql/tmp
init-connect='SET NAMES utf8'
character-set-server=utf8
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
explicit_defaults_for_timestamp=on

back_log=500
max_connections=500
max_connect_errors=10000
thread_cache_size=100
thread_stack=192K
ft_min_word_len=4
lower_case_table_names=1

max_allowed_packet=32M
max_heap_table_size=64M
max_length_for_sort_data=8M
net_buffer_length=65536

join_buffer_size=8M
sort_buffer_size=16M
binlog_cache_size=1M
query_cache_type=0    #如果你查询的表更新比较频繁，而且很少有相同的查询，最好不要使用查询缓存，0——关闭,1——开启,2——只缓存在select语句中通过SQL_CACHE指定需要缓存的查询
query_cache_size=0
tmp_table_size=64M

slave_net_timeout=30
event_scheduler=on

skip-name-resolve      ##ip/localhost
skip-external-locking  ##system lock
skip-slave-start

#key_buffer_size = 384M
#table_open_cache = 512
#read_buffer_size = 2M
#read_rnd_buffer_size = 8M
#myisam_sort_buffer_size = 64M

###***log variables***###
log-bin=/opt/mysql/log/Storage
binlog-format=row
max-binlog-size=500M
expire-logs-days=15
log-error=/opt/mysql/log/Storage.err
relay-log=/opt/mysql/log/Storage-relay-bin
#slow_query_log=1
#slow-query-log-file=/opt/mysql/log/Storage-slow.log
#long_query_time=10
#gtid_mode=off
#enforce_gtid_consistency=0
###sql threads
#slave_parallel_workers=0

###checksum of repl
###default
#binlog_checksum=CRC32
slave_sql_verify_checksum=1
###default off
master_verify_checksum=1
###binlog events info
#binlog_rows_query_log_events=1

###***replication related variables***### 
server-id=1
replicate-ignore-db=mysql
replicate-wild-ignore-table=mysql.%
replicate-ignore-db=sys
replicate-wild-ignore-table=sys.%
#replicate_do_db=dss
#replicate-wild-do-table=dss.%
sync_binlog=1000
slave_load_tmpdir=/opt/mysql/tmp
#log_slave_updates=1
#relay_log_recovery=0
#auto_increment_increment=2
#auto_increment_offset=1
#master_info_repository=table
sync_master_info=10000
sync_relay_log_info=10000
sync_relay_log=1000
#relay_log_info_repository=table

###***InnoDB variables***###
###default value
default-storage-engine=InnoDB
innodb_file_per_table=1
transaction_isolation=REPEATABLE-READ
innodb_buffer_pool_instances=1
innodb_strict_mode=1
innodb_buffer_pool_size=512M
innodb_data_file_path=ibdata1:400M:autoextend
#innodb_temp_data_file_path=ibtmp1:36M:autoextend 
innodb_open_files=2000
innodb_write_io_threads=32
innodb_read_io_threads=32
innodb_thread_concurrency=0
innodb_io_capacity=1200
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=16M
innodb_log_files_in_group=2
innodb_log_file_size=200M
innodb_adaptive_flushing=1
innodb_max_dirty_pages_pct=75
innodb_lock_wait_timeout=120
innodb_fast_shutdown=0
innodb_autoinc_lock_mode=2
#innodb_flush_method=O_DIRECT
innodb_support_xa=ON

###undo###
innodb_undo_directory=/opt/mysql/undo
innodb_undo_tablespaces=4
innodb_undo_logs=128

###deadlocks check
innodb_print_all_deadlocks=1

###default value
innodb_stats_on_metadata=0
innodb_purge_threads=1
innodb_purge_batch_size=300

###default value
innodb_old_blocks_time=1000

###temporary log file (dml) is extended when needed by the value of innodb_sort_buffer_size during alter table
innodb_sort_buffer_size=8M
innodb_online_alter_log_max_size=512M

###default value
performance_schema=1
innodb_stats_persistent=1

###recalculate persistent statistics after the data in a table is changed substantially,The threshold value is currently 10% of the rows in the table
innodb_stats_auto_recalc=1

###***connect/thread timeout variables***###
interactive_timeout=300
wait_timeout=300


[galera]
#wsrep_on=ON
#wsrep_provider=/usr/lib64/galera/libgalera_smm.so
#wsrep_cluster_address=gcomm://192.168.204.135,192.168.204.134,192.168.204.132
#wsrep_log_conflicts=ON
#wsrep_provider_options="gcache.size=128M;"
#wsrep_slave_threads=2
#wsrep_cluster_name='MGC01'
#wsrep_node_address='192.168.204.135'	#在其他节点上，更改此配置值
#wsrep_node_name='Mariadb1'		#在其他节点上，更改此配置值
#wsrep_sst_method=xtrabackup-v2
#wsrep_sst_auth=root:abc@123
#wsrep_sst_method=rsync


[mysqldump]
quick
max_allowed_packet=32M
net_buffer_length=65536

[mysql]
default-character-set=utf8
#prompt="\\u:\\d> "
pager=more
no-auto-rehash

[mysqld_safe]
open-files-limit=65535
user=mysql