MySQL master-slave architecture upgraded from version 5.5 to 5.6 scenarios

Master-Slave architecture [one master multi-slave] upgrade step

1. First install the latest version of MySQL mysql-5.6.26.tar.gz

: Each host directory is installed separately:/usr/local/mysql-5.6

Yum Install Libaio-devel

Compile parameters

/usr/local/cmake/bin/cmake-dcmake_install_prefix=/usr/local/mysql-5.6 \

-dmysql_datadir=/usr/local/mysql-5.6/data \

-dwith_myisam_storage_engine=1 \

-dwith_innobase_storage_engine=1 \

-dwith_archive_storage_engine=1 \

-dwith_blackhole_storage_engine=1 \

-denable_local_infile=1 \

-ddefault_charset=utf8 \

-DDEFAULT_COLLATION=UTF8_GENERAL_CI \

-dextra_charsets=all \

-dmysql_tcp_port=3306 \

-dwith_debug=off \

-dwith_readline=1 \

-dwith_embedded_server=1 \

-dmysql_unix_addr=/tmp/mysql6.sock \

-dwith_ssl=bundled \

-denable_dtrace=off

Make;make Install

Software Installation OK

2. Stop one of the slave libraries:

Copy the Data directory [/usr/local/mysql] from the original version to the directory corresponding to the new version [/usr/local/mysql-5.6]

Cp-r/usr/local/mysql/data/usr/local/mysql-5.6/

3. Change permissions:

Chown-r Root.

Chown-r MySQL Data

4. Change the startup script

Rm-f/etc/init.d/mysqld

cp/usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld-5.5

cp/usr/local/mysql-5.6/support-files/mysql.server/etc/init.d/mysqld-5.6

chmod +x/etc/init.d/mysqld-5.*

5. Start a new version instance

Note: The configuration file must be configured correctly and will cause the instance to fail to start if the old parameters are configured

/etc/init.d/mysqld-5.6 start

Then observe the error file and you will see the following errors:

2015-08-04 13:16:31 18815 [ERROR] Native table ' Performance_schema '. ' Setup_actors ' has the wrong structure

2015-08-04 13:16:31 18815 [ERROR] Native table ' Performance_schema '. ' Setup_objects ' has the wrong structure

2015-08-04 13:16:31 18815 [ERROR] Native table ' Performance_schema '. ' Table_io_waits_summary_by_index_usage ' has the wrong structure

2015-08-04 13:16:31 18815 [ERROR] Native table ' Performance_schema '. ' Table_io_waits_summary_by_table ' has the wrong structure

2015-08-04 13:16:31 18815 [ERROR] Native table ' Performance_schema '. ' Table_lock_waits_summary_by_table ' has the wrong structure

2015-08-04 13:16:31 18815 [ERROR] Column count of Mysql.threads is wrong. expected, found 3. Created with MySQL 50518, now running 50626. Please use the Mysql_upgrade to fix this error.

2015-08-04 13:16:31 18815 [ERROR] Native table ' Performance_schema '. ' Events_stages_current ' has the wrong structure

2015-08-04 13:16:31 18815 [ERROR] Native table ' Performance_schema '. ' Events_stages_history ' has the wrong structure

2015-08-04 13:16:31 18815 [ERROR] Native table ' Performance_schema '. ' Events_stages_history_long ' has the wrong structure

2015-08-04 13:16:31 18815 [ERROR] Native table ' Performance_schema '. ' Events_stages_summary_by_thread_by_event_name ' has the wrong structure

2015-08-04 13:16:31 18815 [ERROR] Native table ' Performance_schema '. ' Events_stages_summary_by_account_by_event_name ' has the wrong structure

2015-08-04 13:16:31 18815 [ERROR] Native table ' Performance_schema '. ' Events_stages_summary_by_user_by_event_name ' has the wrong structure

2015-08-04 13:16:31 18815 [ERROR] Native table ' Performance_schema '. ' Events_stages_summary_by_host_by_event_name ' has the wrong structure

6. Run the upgrade script under the new version for the upgrade check to repair the main tables in the MySQL library

/usr/local/mysql-5.6/bin/mysql_upgrade-u Root-proot

Warning:using a password on the command line interface can is insecure.

Looking for ' MySQL ' as:/usr/local/mysql_5.6/bin/mysql

Looking for ' Mysqlcheck ' as:/usr/local/mysql-5.6/bin/mysqlcheck

Running ' Mysqlcheck ' with connection arguments: '--port=3306 '--socket=/tmp/mysql6.sock '

Warning:using a password on the command line interface can is insecure.

Running ' Mysqlcheck ' with connection arguments: '--port=3306 '--socket=/tmp/mysql6.sock '

Warning:using a password on the command line interface can is insecure.

Mysql.columns_priv OK

Mysql.db OK

Mysql.event OK

Mysql.func OK

Mysql.general_log OK

Mysql.help_category OK

Mysql.help_keyword OK

Mysql.help_relation OK

Mysql.help_topic OK

Mysql.host 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.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

Running ' Mysql_fix_privilege_tables ' ...

Warning:using a password on the command line interface can is insecure.

Running ' Mysqlcheck ' with connection arguments: '--port=3306 '--socket=/tmp/mysql6.sock '

Warning:using a password on the command line interface can is insecure.

Running ' Mysqlcheck ' with connection arguments: '--port=3306 '--socket=/tmp/mysql6.sock '

Warning:using a password on the command line interface can is insecure.

BABYSITTER.ABC OK

Babysitter.account_tactics_daily_availability OK

Babysitter.andtlhz_new OK

7. Restart the instance after the check is complete

/etc/init.d/mysqld-5.6 restart

Configuration file

[Client]

#default-character-set = UTF8

Port = 3306

Socket =/tmp/mysql6.sock

[Mysqld]

#skip-grant-tables

user = MySQL

Port = 3306

Socket =/tmp/mysql6.sock

Pid-file =/usr/local/mysql-5.6/data/mysql-upgrade-master.pid

#pid-file =/usr/local/mysql-5.6/data/[Host name].pid

#pid-file =/usr/local/mysql-5.6/data/[Host name].pid

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

Basedir =/usr/local/mysql-5.6

DataDir =/usr/local/mysql-5.6/data

Server-id = 1

#server-id = 2 #从库1配置

#server-id = 3 #从库2配置

Log_slave_updates = 1

log_slave_updates = 0 #从库配置

Log-bin = Mysql-bin

#log-bin = Mysql-bin #从库不需要开启binlog

Binlog_format = Mixed

Binlog_cache_size = 64M

Max_binlog_cache_size = 128M

Expire_logs_days = 2

Max_binlog_size = 1G

binlog-ignore-db = MySQL

BINLOG-IGNORE-DB = Test

Binlog-ignore-db = Information_schema

Binlog-ignore-db = Performance_schema

Query_cache_type =0

Key_buffer_size = 384M

Sort_buffer_size = 2M

Read_buffer_size = 2M

Read_rnd_buffer_size = 16M

Join_buffer_size = 2M

Thread_cache_size = 8

Query_cache_size = 32M

Query_cache_limit = 2M

Query_cache_min_res_unit = 2K

Thread_concurrency = 32

Table_open_cache = 512

Open_files_limit = 10240

Back_log = 600

Max_connections = 5000

Max_connect_errors = 6000

external-locking = FALSE

Max_allowed_packet = 10M

Default-storage-engine = MYISAM

Thread_stack = 192K

Transaction_isolation = read-committed

Tmp_table_size = 256M

Max_heap_table_size = 512M

Bulk_insert_buffer_size = 64M

Long_query_time = 2

Slow_query_log

Slow_query_log_file =/usr/local/mysql-5.6/data/slow_query.log

Skip-name-resolve

Explicit_defaults_for_timestamp = True #新版本关于时间戳的新特性配置

Innodb_buffer_pool_size = 512M

Innodb_data_file_path = Ibdata1:256m:autoextend

Innodb_file_io_threads = 4

Innodb_thread_concurrency = 8

Innodb_flush_log_at_trx_commit = 2

Innodb_log_buffer_size = 16M

Innodb_log_file_size = 128M

Innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

Innodb_lock_wait_timeout = 120

innodb_file_per_table = 1

Innodb_flush_method = O_direct

[Mysqldump]

Quick

Max_allowed_packet = 10M

[MySQL]

No-auto-rehash

Safe-updates

[Mysqlhotcopy]

Interactive-timeout

[Myisamchk]

Key_buffer_size = 256M

Sort_buffer_size = 256M

Read_buffer = 2M

Write_buffer = 2M

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

The above sequence of operations is: from 1 "from 2" main

Pay special attention to the correctness of the configuration file

Enough disk space to hold two old data sizes

Old data does not move, in case the upgrade fails, can be rolled back to the old version

This article is from "Shadow Knight" blog, please make sure to keep this source http://andylhz2009.blog.51cto.com/728703/1844007

MySQL master-slave architecture upgraded from version 5.5 to 5.6 scenarios