MySQL5.6.26 upgrade to MySQL5.7.9 Combat solution "turn"

MySQL5.6.26 upgrade to MySQL5.7.9 Combat solution

Turn from

MySQL5.6.26 upgrade to MySQL5.7.9 Combat Solutions-Other networking technologies-Red and Black Alliance
Http://www.2cto.com/net/201512/453682.html

Objective

A large company upgrade plan, because the company often security scan, each version has its own bug, in order to secure to a certain time will be upgraded to a new version. In this case, a single-machine environment upgrade scheme is used to build master-slave scheme.

1. Stand-alone environment

ip:172.16.0.111

System: SUSE 11

MySQL old version: 5.6.26

MySQL new version: 5.7.9

2. Pre-Upgrade Preparation

Note: Before upgrading, look for disk space sufficient to be able to build from the library in this machine.

3. Install the new version

Old version Directory

Software Catalog:/app/mysql5.6/

Data Catalog:/data/mysql3306/

Configuration file:/app/mysql5.6/my.cnf

[Client]

Socket=/app/mysql5.6/mysql.sock

Default-character-set=utf8

#innodb_buffer_pool_size

port=3306

[MySQL]

Prompt=\\[email protected]\\d \\r:\\m:\\s>

No-auto-rehash

[Mysqld_safe]

Pid-file=/app/mysql5.6/mysqld.pid

[Mysqld]

Socket=/app/mysql5.6/mysql.sock

port=3306

Character-set-server=utf8

transaction-isolation=read-committed

Sql_mode=no_engine_substitution,strict_trans_tables

Explicit_defaults_for_timestamp=true

Lower_case_table_names=1

Local-infile=1

Skip-name-resolve

table_definition_cache=500

table_open_cache=500

max_connections=1024

max_user_connections=1000

max_connect_errors=65536

max_allowed_packet=16m

Query_cache_type=0

basedir=/app/mysql5.6

datadir=/data/mysql3306

Slow_query_log=1

Long_query_time=1

Log-queries-not-using-indexes

#############

Event_scheduler=on

#innodb_buffer_pool_size =20g

innodb_flush_log_at_trx_commit=2

Innodb_strict_mode=1

Innodb_flush_method=o_direct

Innodb_log_files_in_group=3

Innodb_file_per_table

Log-bin=mysql-bin

Binlog-format=row

server_id=111

Log-slave-updates=1

Skip-slave-start

#read_only =1

Expire_logs_days=7

Sync_binlog=1

New version Directory

Software Catalog:/app/mysql5.7/

Data Catalog:/data/mysqldata/

Configuration file:/app/mysql5.7/my.cnf

3.1. Extracting files

mysql01:~# Tar zxvf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz

3.2. Move the extracted files

mysql01:~ # mvmysql-5.7.9-linux-glibc2.5-x86_64/app/mysql5.7

mysql01:~ # Chown-r mysql:app/app/mysql5.7

3.3. Create a Data Catalog

mysql01:~ # Mkdir/data/mysqldata

mysql01:~ # Chown-r Mysql:app/data/mysqldata

3.4. Initializing data

Note: Initialization is not the same as the previous version, the initialization password is initialized with the

mysql01:/data/mysqldata#/app/mysql5.7/bin/mysqld--initialize--user=mysql--basedir=/app/mysql5.7--datadir=/data /mysqldata

2015-12-03t23:34:12.688857z0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Use--explicit_defaults_for_timestamp server option (see documentation for Moredetails).

2015-12-03t23:34:12.927838z0 [Warning] innodb:new log files created, lsn=45790

2015-12-03t23:34:12.968656z0 [Warning] innodb:creating FOREIGN KEY constraint system tables.

2015-12-03t23:34:13.025810z0 [Warning] No existing UUID have been found, so we assume it is thefirst time Server has been started. Generating a new uuid:5c20785f-9a16-11e5-a525-000c294cec8f.

2015-12-03t23:34:13.027457z0 [Warning] Gtid table is not a ready-to-be used. Table ' mysql.gtid_executed ' cannot be opened.

2015-12-03T23:34:13.028709Z1 [Note] A temporary password is generated for [email protected]: O-fxrjjl_1yi #初始化密码

3.5. Modify the configuration file

mysql01:~ # CP/APP/MYSQL5.6/MY.CNF/APP/MYSQL5.7/MY.CNF

Mysql01:/data/mysqldata # VI/APP/MYSQL5.7/MY.CNF

[Client]

Socket=/app/mysql5.7/mysql.sock

Default-character-set=utf8

port=3307

[MySQL]

Prompt=\\[email protected]\\d \\r:\\m:\\s>

No-auto-rehash

[Mysqld_safe]

Pid-file=/app/mysql5.7/mysqld.pid

[Mysqld]

Socket=/app/mysql5.7/mysql.sock

port=3307

Character-set-server=utf8

transaction-isolation=read-committed

Sql_mode=no_engine_substitution,strict_trans_tables

Explicit_defaults_for_timestamp=true

Lower_case_table_names=1

Local-infile=1

Skip-name-resolve

table_definition_cache=500

table_open_cache=500

max_connections=1024

max_user_connections=1000

max_connect_errors=65536

max_allowed_packet=16m

Query_cache_type=0

basedir=/app/mysql5.7

Datadir=/data/mysqldata

Slow_query_log=1

Long_query_time=1

Log-queries-not-using-indexes

###################

#############

Event_scheduler=on

#innodb_buffer_pool_size =20g

innodb_flush_log_at_trx_commit=2

Innodb_strict_mode=1

Innodb_flush_method=o_direct

Innodb_log_files_in_group=3

Innodb_file_per_table

Log-bin=mysql-bin

Binlog-format=row

server_id=112

Log-slave-updates=1

Skip-slave-start

#read_only =1

Expire_logs_days=7

Sync_binlog=1

3.6 Modifying the startup script

Mysql01:/data/mysqldata # cp/app/mysql5.7/support-files/mysql.server/etc/init.d/mysql3307

Mysql01:/data/mysqldata # vi/etc/init.d/mysql3307

The place of modification

basedir=/app/mysql5.7

Datadir=/data/mysqldata

Mysql01:/data/mysqldata # chmod +x/etc/init.d/mysql3307

Start a new version of MySQL

Mysql01:/data/mysqldata #/etc/init.d/mysql3307 Start

Starting MySQL. Done

Go to MySQL

Mysql01:/data/mysqldata #/app/mysql5.7/bin/mysql-uroot-po-fxrjjl_1yi-p3307--socket=/app/mysql5.7/mysql.sock

4. Exporting and importing data

Exporting data

[Email protected] ~ $mysqldump-uroot-p--default-character-set=utf8--all-databases--single-transaction--routines-- Triggers--events--master-data=2--socket=/app/mysql5.6/mysql.sock >mysql20151211.sql

Annotations:

--all-databases Exporting all databases

--single-transaction

This option submits a BEGINSQL statement before the data is exported, and begin does not block any applications and ensures consistent state of the database at the time of export. It only works with multiple versions of the storage engine, only InnoDB. This option and the--lock-tables option are mutually exclusive, because locktables causes any pending transactions to be implicitly committed. To export large tables, you should use the--quick option together.

--routines,-r

Export stored procedures and custom functions.

--triggers

Export the trigger. This option is enabled by default and is disabled with--skip-triggers.

--events,-e

Export events.

--master-data

This option appends the location and file name of the Binlog to the output file. If 1, the Changemaster command is output, and if 2, the output of the Changemaster command is added before the comment information. This option turns on the--lock-all-tables option unless--single-transaction is also specified (in this case, the global read lock gets a short time when the export is started; the other contents refer to the--single-transaction option below). This option automatically turns off the--lock-tables option.

[Email protected] ~ $more Mysql20151211.sql

--MySQL dump 10.13 distrib 5.6.26, for linux-glibc2.5 (x86_64)

--

--Host:localhost Database:

-- ------------------------------------------------------

--Server version 5.6.26-log

/*!40101 [email protected][email protected] @CHARACTER_SET_CLIENT */;

/*!40101 [email protected][email protected] @CHARACTER_SET_RESULTS */;

/*!40101 [email protected][email protected] @COLLATION_CONNECTION */;

/*!40101 SET NAMES UTF8 */;

/*!40103 SET @[email protected] @TIME_ZONE */;

/*!40103 SET time_zone= ' +00:00 ' */;

/*!40014 [email protected][email protected] @UNIQUE_CHECKS, unique_checks=0 */;

/*!40014 SET @[email protected] @FOREIGN_KEY_CHECKS, foreign_key_checks=0

*/;

/*!40101 SET @[email protected] @SQL_MODE, sql_mode= ' no_auto_value_on_zero ' */;

/*!40111 SET @[email protected] @SQL_NOTES, sql_notes=0 */;

--

--Position to start replication orpoint-in-time recovery from

--

--Change MASTER tomaster_log_file= ' mysql-bin.000049 ', master_log_pos=120;

#主从搭建时需要的

Import backup data to a new version

[Email protected] ~ $/app/mysql5.7/bin/mysql-uroot-po-fxrjjl_1yi-p3307--socket=/app/mysql5.7/mysql.sock < Mysql20151211.sql

View Import Success

[Email protected] ~ $/app/mysql5.7/bin/mysql-uroot-po-fxrjjl_1yi-p3307--socket=/app/mysql5.7/mysql.sock

MySQL: [Warning] Using a password on Thecommand line interface can be insecure.

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 6

Server version:5.7.9-log MySQL communityserver (GPL)

Copyright (c), Oracle and/or itsaffiliates. All rights reserved.

Oracle is a registered trademark of oraclecorporation and/or its

Affiliates. Other names trademarksof their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' toclear the current input statement.

[Email protected] (none) 07:51:16>show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| Dataaudit |

| Fanrenjie |

| Huizhe |

| MySQL |

| Performance_schema |

| SYS |

| Test |

| test02 |

| test08 |

+--------------------+

Rows in Set (0.00 sec)

[Email protected] (none) 07:51:20>

5. Master-Slave construction

Add an account for replication on master:

[Email protected] (none) 07:52:47>grant replicationslave on * * to ' repl ' @ ' 172.16.0.% ' identified by ' 123456 ';

Query OK, 0 rows affected (1.01 sec)

Execute the following command on the slave

[Email protected] ~ $/app/mysql5.7/bin/mysql-uroot-po-fxrjjl_1yi-p3307--socket=/app/mysql5.7/mysql.sock

MySQL: [Warning] Using a password on Thecommand line interface can be insecure.

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 6

Server version:5.7.9-log MySQL communityserver (GPL)

Copyright (c), Oracle and/or itsaffiliates. All rights reserved.

Oracle is a registered trademark of oraclecorporation and/or its

Affiliates. Other names trademarksof their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' toclear the current input statement.

[Email protected] (none) 07:51:20>change MASTER tomaster_host= ' 172.16.0.111 ', master_port=3306,master_user= ' repl ', Master_password = ' 123456 ', master_log_file= ' mysql-bin.000049 ', master_log_pos=120;

#注意MASTER_LOG_FILE = ' mysql-bin.000049 ', master_log_pos=120; As in the backup data, in order to ensure data consistency

Query OK, 0 rows affected, 2 warnings (0.01SEC)

[Email protected] (none) 08:00:17>start slave; #开启主从

Query OK, 0 rows Affected (0.00 sec)

[Email protected] (none) 08:03:42>show slavestatus\g; #查看复制

1. row***************************

Slave_io_state:waiting Formaster to send event

master_host:172.16.0.111

Master_user:repl

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000049

read_master_log_pos:327

relay_log_file:mysql01-relay-bin.000002

relay_log_pos:524

relay_master_log_file:mysql-bin.000049

Slave_io_running:yes

Slave_sql_running:yes

replicate_do_db:

replicate_ignore_db:

Replicate_do_table:

Replicate_ignore_table:

Replicate_wild_do_table:

Replicate_wild_ignore_table:

last_errno:0

Last_error:

skip_counter:0

exec_master_log_pos:327

relay_log_space:733

Until_condition:none

Until_log_file:

until_log_pos:0

Master_ssl_allowed:no

Master_ssl_ca_file:

Master_ssl_ca_path:

Master_ssl_cert:

Master_ssl_cipher:

Master_ssl_key:

seconds_behind_master:0

Master_ssl_verify_server_cert:no

last_io_errno:0

Last_io_error:

last_sql_errno:0

Last_sql_error:

Replicate_ignore_server_ids:

master_server_id:111

master_uuid:279f439b-5d2f-11e5-ad29-000c294cec8f

Master_info_file:/data/mysqldata/master.info

sql_delay:0

Sql_remaining_delay:null

Slave_sql_running_state:slave have read all relay log; Waiting for Moreupdates

master_retry_count:86400

Master_bind:

Last_io_error_timestamp:

Last_sql_error_timestamp:

MASTER_SSL_CRL:

Master_ssl_crlpath:

Retrieved_gtid_set:

Executed_gtid_set:

auto_position:0

replicate_rewrite_db:

Channel_name:

1 row in Set (0.00 sec)

ERROR:

No query specified

[Email protected] (none) 08:03:55>

6. Execute Mysql_upgrade command

Official mentions that executing this command allows the original data to be enabled to the new version of the feature, note: Gtid replication do not open

[Email protected] ~$/app/mysql5.7/bin/mysql_upgrade-uroot-p123456-p3307--socket=/app/mysql5.7/mysql.sock

Mysql_upgrade: [Warning] Using a Passwordon 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.0).

Found 0 sys functions, but expected 21.re-installing the SYS schema.

Upgrading the SYS schema.

Checking databases.

dataaudit.t_dataaudit_amount_7 OK

Fanrenjie.hz_admin OK

Fanrenjie.hz_advertisement OK

Fanrenjie.hz_car OK

Fanrenjie.hz_education OK

Fanrenjie.hz_entertainment OK

Fanrenjie.hz_food OK

Fanrenjie.hz_health OK

Fanrenjie.hz_house OK

Fanrenjie.hz_it OK

Fanrenjie.hz_literature OK

Fanrenjie.hz_manager_menu OK

Fanrenjie.hz_message OK

Fanrenjie.hz_news OK

Fanrenjie.hz_sports OK

Fanrenjie.hz_subclass_menu OK

Fanrenjie.hz_travel OK

Huizhe.auth_urllink OK

Huizhe.auth_user OK

Huizhe.data_baoyou OK

Huizhe.data_huazhuangpin OK

Huizhe.data_muying OK

Huizhe.data_nanzhuang OK

Huizhe.data_nvzhuang OK

Huizhe.data_products OK

Huizhe.data_shipin OK

Huizhe.data_tour OK

Huizhe.data_tuan OK

Huizhe.data_xiebao OK

Sys.sys_config OK

Upgrade process completed successfully.

Checking if update is needed.

7. Upgrade the switch.

To stop the main library and stop the library, the port is modified to 3306 because there is no effect on the production library. You must contact the app when you stop.

Stop the main library

Mysql01:/data/mysqldata #/etc/init.d/mysqld Stop

Shutting down Mysql....done

Stop from Library

Mysql01:/data/mysqldata #/etc/init.d/mysql3307 Stop

Shutting down Mysql....done

Modify Startup

Mysql01:/data/mysqldata # cd/etc/init.d/

MYSQL01:/ETC/INIT.D # mv Mysqld Mysqlold

MYSQL01:/ETC/INIT.D # mv mysql3307 mysqld

Modifying a configuration file

MYSQL01:/ETC/INIT.D # VI/APP/MYSQL5.7/MY.CNF

[Client]

....

port=3306

...

[Mysqld]

............

port=3306

......

Finally start a new version

MYSQL01:/ETC/INIT.D #/etc/init.d/mysqldstart

Starting MySQL. Done

Clear synchronization

Mysql> Resetslave All;

Query OK, 0 rows Affected (0.00 sec)

Mysql> show Slave status\g;

Empty Set (0.00 sec)

ERROR:

No query specified

Modify the environment variables as follows

[Email protected] ~ $vi. Profile

Export mysql_home=/app/mysql5.7

Export my_basedir_version=/app/mysql5.7

Export Path=/app/mysql5.7/bin: $PATH

Export Ld_library_path=/app/mysql5.7/lib

Export ps1= ' \[email protected]\h \w \$ '

Make environment variables effective

[Email protected] ~ $source. Profile

Note: After a few days if the app is all right.

Finally clean up the old version of the data and software

MySQL5.6.26 upgrade to MySQL5.7.9 Combat solution "turn"