Mysql version upgrade

Mysql version upgrade

Current version: 5.7.18
Upgraded version: 5.7.19

1. Copy the new version to the specified directory.
[Root @ izwz9gxsbwybwg9n5xdi47z soft] # cp mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz/opt/

2. Extract
[Root @ izwz9gxsbwybwg9n5xdi47z opt] # tar-zxvf mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
[Root @ izwz9gxsbwybwg9n5xdi47z opt] # Music mysql-5.7.19-linux-glibc2.12-x86_64 mysql5719

3. Create a directory (based on the configuration file of your database)
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. Copy the original configuration file to the new directory.
[Root @ izwz9gxsbwybwg9n5xdi47z conf] # cp my. cnf/opt/mysql5719/db1/conf/


5. modify the configuration file
[Mysqld]
Port = 3306
Server-id = 1
Datadir =/opt/mysql5718/db1/data # the data file path does not need to be changed. The database is large and it takes time to copy data to a new place. The original path is used directly.
Character-set-server = utf8
Max_connections = 100
Skip-external-locking
Key_buffer_size = 8 M
Max_allowed_packet = 16 M
Myisam_sort_buffer_size = 16 M
Query_cache_size = 16 M
Read_buffer_size = 2 M
Sort_buffer_size = 2 M
# 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 = 16 M
Innodb_buffer_pool_size = 16 M
Innodb_flush_log_at_trx_commit = 0
Innodb_lock_wait_timeout = 50
Innodb_log_buffer_size = 32 M
Innodb_log_file_size = 32 M
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 = 100 M
Log-bin =/opt/mysql5719/db1/mysqllog/binlog. bin # specify the new path
Slow_query_log_file =/opt/mysql5719/db1/mysqllog/logfile/slow-query.log # specify to the new path
Long_query_time = 1
Log-error =/opt/mysql5719/db1/mysqllog/logfile/mysql-err.log # specify to the new path
Binlog_format = row
Expire_logs_days = 7
Binlog_cache_size = 4 MB
Skip-host-cache
# Skip-name-resolve
# Read-only
Skip-slave-start
Relay-log-index =/opt/mysql5719/db1/mysqllog/relaylog/slave-relay-bin.index # specify to the new path
Relay-log =/opt/mysql5719/db1/mysqllog/relaylog
Replicate-ignore-db = information_schema, cece_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 = 16 M
[Myisamchk]
Key_buffer_size = 8 M
Sort_buffer_size = 8 M
Read_buffer = 4 M
Write_buffer = 4 M


6. Grant Permissions
[Root @ izwz9gxsbwybwg9n5xdi47z opt] # chown-R mysql: mysql./mysql5719


7. Start
./Mysqld_safe -- defaults-file =/opt/mysql5719/db1/conf/my. cnf -- user = mysql


8. Run mysql_upgrade to upgrade the data dictionary.
./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. log in to view the version
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-


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

-- The End --