MySQL主从架构由5.5版本升级到5.6方案-低调大师

MySQL主从架构由5.5版本升级到5.6方案

2017-11-02 759

主从架构[一主多从]升级步骤

1. 首先安装最新版本的MySQL mysql-5.6.26.tar.gz

：每台主机分别安装目录：/usr/local/mysql-5.6

yum install libaio-devel

编译参数

/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

软件安装OK

2. 停止其中一个从库：

将原版本中的数据[data]目录[/usr/local/mysql]拷贝到新版本对应的目录下面[/usr/local/mysql-5.6]

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

3. 变更权限：

chown -R root .

chown -R mysql data

4. 变更启动脚本

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.启动新版本实例

注意：配置文件必须配置正确，如果配置了旧的参数，会导致实例无法启动

/etc/init.d/mysqld-5.6 start

然后观察错误文件，会看到如下报错：

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 14, found 3. Created with MySQL 50518, now running 50626. Please use 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. 运行新版本下的升级脚本进行升级检查，修复mysql库中的主要表

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

Warning: Using a password on the command line interface can be 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 be insecure.

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

Warning: Using a password on the command line interface can be 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 be insecure.

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

Warning: Using a password on the command line interface can be insecure.

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

Warning: Using a password on the command line interface can be insecure.

babysitter.abc OK

babysitter.account_tactics_daily_availability OK

babysitter.andtlhz_new OK

7. 检查完后重启实例

/etc/init.d/mysqld-5.6 restart

配置文件

[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/[主机名].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

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

以上操作顺序为：从1》从2》主

特别注意配置文件的正确性

磁盘空间足够存放两份旧数据的大小

旧数据不动，以防升级失败，可以回退到旧版本

本文转自andylhz 51CTO博客，原文链接：http://blog.51cto.com/andylhz2009/1844007，如需转载请自行联系原作者

微信关注我们

原文链接：https://yq.aliyun.com/articles/447361

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

iOS逆向工程之App脱壳

一、生成dumpdecrypted.dylib动态库首先我们要生成“砸壳”用的动态库dumpdecrypted.dylib，我们“砸壳”时主要用到这个动态库。该动态库的源码在github上是开源的（Github地址），要想得到dumpdecrypted.dylib这个动态库，只需要从github上下载代码，然后进行编译，生成dumpdecrypted.dylib即可。下方就是使用make命令对dumpdecrypted的源码进行编译。进入到dumpdecrypted文件夹下，运行make命令即可。编译完成后，dumpdecrypted文件夹中就会多一个dumpdecrypted.dylib动态库，该动态库就是稍后我们砸壳使用的工具。二、“砸壳”前的准备工作接下来就是要在越狱手机上寻找要“砸壳”的app路径了，首先使用ssh连接到你的越狱设备，然后在越狱设备中打开你要砸壳的App, 此处我们以微信为例。从AippStore中下载安装的应用都会位于/var/mobile/Applications中，下方的截图就是我手机上从AppStore中下载的应用的安装路径。当然，下方是...

2017-11-02

680

0) 下载 http://www.isc.org/software/bind 1) 解压缩 tar -zxvf bind-9.3.P2.tar.gz –C /tmp cd /tmp/bind-9.3.P2 2) 配置 ./configure --prefix=/data/sdns/named --enable-threads --with-openssl=no 3) 编译安装 make;make install 4) 建立配置文件 cd /data/sdns/named 5) 生成rndc控制命令的key文件 sbin/rndc-confgen > etc/rndc.conf 6) 从rndc.conf中提取named.conf用的key tail -10 etc/rndc.conf | head -9 | sed s/#\ //g > etc/named.conf 7) 配置named.conf加如下代码 vi /data/sdns/named/etc/named.conf 增加 options { d...

2017-11-03

796

资源下载

更多资源

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

WebStorm

WebStorm 是jetbrains公司旗下一款JavaScript 开发工具。目前已经被广大中国JS开发者誉为“Web前端开发神器”、“最强大的HTML5编辑器”、“最智能的JavaScript IDE”等。与IntelliJ IDEA同源，继承了IntelliJ IDEA强大的JS部分的功能。