mysql版本升級

mysql版本升級

目前的版本:5.7.18
升級後版本:5.7.19

1.拷貝新版本檔案到指定目錄
[root@izwz9gxsbwybwg9n5xdi47z soft]# cp mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz /opt/

2.解壓縮
[root@izwz9gxsbwybwg9n5xdi47z opt]# tar -zxvf mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
[root@izwz9gxsbwybwg9n5xdi47z opt]# mv mysql-5.7.19-linux-glibc2.12-x86_64 mysql5719

3.建立相應的目錄(根據自己資料庫的設定檔建立)
mkdir -p /opt/mysql5719/db1/conf
mkdir -p /opt/mysql5719/db1/redolog
mkdir -p /opt/mysql5719/db1/mysqllog/binlog
mkdir -p /opt/mysql5719/db1/mysqllog/logfile
mkdir -p /opt/mysql5719/db1/mysqllog/relaylog


4.拷貝原來的設定檔到新的目錄
[root@izwz9gxsbwybwg9n5xdi47z conf]# cp my.cnf /opt/mysql5719/db1/conf/


5.修改設定檔
[mysqld]
port=3306
server-id=1
datadir=/opt/mysql5718/db1/data ##資料檔案路徑不需要改變，資料庫比較龐大，拷貝到新地方比較消耗時間，直接使用原有路徑
character-set-server=utf8
max_connections = 100
skip-external-locking
key_buffer_size=8M
max_allowed_packet=16M
myisam_sort_buffer_size=16M
query_cache_size=16M
read_buffer_size=2M
sort_buffer_size=2M
#table_cache=512
#thread_cache=20
#thread_concurrency=4
interactive_timeout=86400
wait_timeout=86400
#log_slow_queries=1
innodb_file_per_table=1
#innodb_additional_mem_pool_size=16M
innodb_buffer_pool_size=16M
innodb_flush_log_at_trx_commit=0
innodb_lock_wait_timeout=50
innodb_log_buffer_size=32M
innodb_log_file_size=32M
innodb_log_files_in_group=5
innodb_log_group_home_dir=/opt/mysql5719/db1/redolog
innodb_thread_concurrency=8
log_bin_trust_function_creators=1
event_scheduler=1
max_binlog_size=100M
log-bin=/opt/mysql5719/db1/mysqllog/binlog/binlog.bin ##指定到新的路徑
slow_query_log_file=/opt/mysql5719/db1/mysqllog/logfile/slow-query.log ##指定到新的路徑
long_query_time=1
log-error=/opt/mysql5719/db1/mysqllog/logfile/mysql-err.log ##指定到新的路徑
binlog_format=row
expire_logs_days=7
binlog_cache_size=4MB
skip-host-cache
#skip-name-resolve
#read-only
skip-slave-start
relay-log-index=/opt/mysql5719/db1/mysqllog/relaylog/slave-relay-bin.index ##指定到新的路徑
relay-log=/opt/mysql5719/db1/mysqllog/relaylog
replicate-ignore-db=information_schema,performance_schema
slave_net_timeout=60
log_slave_updates=1
##lower_case_table_names=1
language=/opt/mysql5719/share/english
early-plugin-load=""
explicit_defaults_for_timestamp=true


[client]
port = 3306
socket=/opt/mysql5719/db1/mysql.sock
default-character-set = utf8


[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M


6.賦予許可權
[root@izwz9gxsbwybwg9n5xdi47z opt]# chown -R mysql:mysql ./mysql5719


7.啟動
./mysqld_safe --defaults-file=/opt/mysql5719/db1/conf/my.cnf --user=mysql


8.運行mysql_upgrade升級資料字典
./mysql_upgrade -h localhost -uroot -pmysql -P3306
[root@izwz9gxsbwybwg9n5xdi47z bin]# ./mysql_upgrade -h localhost -uroot -pmysql -P3306
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
The sys schema is already up to date (version 1.5.1).
Checking databases.
ngoss_dim.dim_query_terminal_config OK
ngoss_dim.dim_query_terminal_config_def OK
ngoss_dim.dm_query_terminal_collcet_config OK
ngoss_dim.dm_query_terminal_collcet_config_def OK
ngoss_dim.tb_test0707 OK
ngoss_dim.tb_test_innodb OK
ngoss_dim.tb_test_myisam OK
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.




9.登陸查看版本
mysql> use ngoss_dim;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.19-log |
+------------+
1 row in set (0.00 sec)

-- The End --