2015-12-03T23:34:12.688857Z0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please 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 has been found, so we assume that this is thefirst time that this server has been started. Generating a new UUID:5c20785f-9a16-11e5-a525-000c294cec8f.
2015-12-03T23:34:13.027457Z0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed'cannot be opened.
2015-12-03T23:34:13.028709Z1 [Note] A temporary password is generated for root@localhost: O-FxRJjl_1Yi #初始化密码
mysql@mysql01 ~ $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 SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET@OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@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;
#主从搭建时需要的
把备份数据导入新版本
mysql@mysql01 ~ $/app/mysql5.7/bin/mysql-uroot -pO-FxRJjl_1Yi -P3307 --socket=/app/mysql5.7/mysql.sock < mysql20151211.sql
查看导入成功
mysql@mysql01 ~ $/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) 2000, 2015, Oracle and/or itsaffiliates. All rights reserved.
Oracle is a registered trademark of OracleCorporation and/or its
affiliates. Other names may be trademarksof their respective
owners.
Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
root@(none) 07:51:16>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dataaudit |
| fanrenjie |
| huizhe |
| mysql |
| performance_schema |
| sys |
| test |
| test02 |
| test08 |
+--------------------+
10 rows in set (0.00 sec)
root@(none) 07:51:20>
5.主从搭建
在Master上增加一个用于复制的账号：
root@(none) 07:52:47>GRANT REPLICATIONSLAVE ON *.* TO 'repl'@'172.16.0.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (1.01 sec)
在SLAVE上执行如下命令
mysql@mysql01 ~ $/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) 2000, 2015, Oracle and/or itsaffiliates. All rights reserved.
Oracle is a registered trademark of OracleCorporation and/or its
affiliates. Other names may be trademarksof their respective
owners.
Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
root@(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;与备份数据里的要一样，为了保证数据一致性
Query OK, 0 rows affected, 2 warnings (0.01sec)
root@(none) 08:00:17>start slave; #开启主从
Query OK, 0 rows affected (0.00 sec)
root@(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 has 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
root@(none) 08:03:55>
6.执行mysql_upgrade命令 官方提到执行这命令可以让原数据启用到新版本的特性，注意：GTID复制不要开启
mysql@mysql01 ~$/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.升级切换。 把主库停止，把从库停止后，端口修改成3306，因为为了对生产库没有影响。在停止时必须和应用联系。
停止主库
mysql01:/data/mysqldata #/etc/init.d/mysqld stop
Shutting down MySQL.... done
停止从库
mysql01:/data/mysqldata #/etc/init.d/mysql3307 stop
Shutting down MySQL.... done
修改启动
mysql01:/data/mysqldata # cd /etc/init.d/
mysql01:/etc/init.d # mv mysqld mysqlold
mysql01:/etc/init.d # mv mysql3307 mysqld
修改配置文件
mysql01:/etc/init.d # vi/app/mysql5.7/my.cnf
[client]
….
port=3306
…
[mysqld]
…………
port=3306
……
数据库
发布于：2021-07-03 21:45
|
阅读数：302
|
评论：0
QQ好友和群
QQ空间
