1. upgrade 스크립트 파일을 만든다.



ELLORDNET-MPR:~ ellord$ vi upgrademysql.sh



#!/bin/sh


wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.16-osx10.11-x86_64.tar.gz

tar xfvz mysql-5.7.16-osx10.11-x86_64.tar.gz



echo "stopping mamp"

sudo /Applications/MAMP/bin/stop.sh

sudo killall httpd mysqld


echo "creating backup"

sudo rsync -arv --progress /Applications/MAMP ~/Desktop/MAMP-Backup


echo "copy bin"

sudo rsync -arv --progress mysql-5.7.*/bin/* /Applications/MAMP/Library/bin/ --exclude=mysqld_multi --exclude=mysqld_safe


echo "copy share"

sudo rsync -arv --progress mysql-5.7.*/share/* /Applications/MAMP/Library/share/


echo "fixing access (workaround)"

sudo chmod -R o+rw  /Applications/MAMP/db/mysql/

sudo chmod -R o+rw  /Applications/MAMP/tmp/mysql/


echo "starting mamp"

sudo /Applications/MAMP/bin/start.sh


echo "migrate to new version"

sudo chmod -R 777 /Applications/MAMP/db/mysql/

/Applications/MAMP/Library/bin/mysql_upgrade --user=root --password=root --host=localhost --port=3306



2. 스크립트를 실행한다.


ELLORDNET-MPR:~ ellord$ ./upgrademysql.sh



-2016-11-17 10:22:55--  http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.16-osx10.11-x86_64.tar.gz

Resolving dev.mysql.com (dev.mysql.com)... 137.254.60.11

Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:80... connected.

HTTP request sent, awaiting response... 302 Found

Location: http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.16-osx10.11-x86_64.tar.gz [following]

--2016-11-17 10:22:56--  http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.16-osx10.11-x86_64.tar.gz

Resolving cdn.mysql.com (cdn.mysql.com)... 104.76.99.248

Connecting to cdn.mysql.com (cdn.mysql.com)|104.76.99.248|:80... connected.

HTTP request sent, awaiting response... 200 OK

Length: 331502302 (316M) [application/x-tar-gz]

Saving to: ‘mysql-5.7.16-osx10.11-x86_64.tar.gz.2’


 9% [========>                                                                                        ] 32,386,761  4.60MB/s  eta 64s    



아래와 같이 비밀번호를 입력하라고 하면.


stopping mamp

Password:



맥의 비밀번호를 입력해 준다.




마지막에 아래와 같은 에러가 나면.


migrate to new version

chmod: /Applications/MAMP/db/mysql/: No such file or directory

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

mysql_upgrade: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) while connecting to the MySQL server

Upgrade process encountered error and will not continue.

ELLORDNET-MPR:Documents ellord$ 161117 10:24:48 mysqld_safe Starting mysqld daemon with databases from /Applications/MAMP/db/mysql56

161117 10:24:49 mysqld_safe mysqld from pid file /Applications/MAMP/tmp/mysql/mysql.pid ended



mysql_upgrade 명령을 수동으로 실행한다.


ELLORDNET-MPR:bin ellord$ cd /Application/MAMP/Library/bin

ELLORDNET-MPR:bin ellord$ ./mysql_upgrade -u root 

mysql_upgrade: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) while connecting to the MySQL server

Upgrade process encountered error and will not continue.

ELLORDNET-MPR:bin ellord$ ./mysql_upgrade -u root --password=didckswn

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.

mysql_upgrade: [ERROR] 1146: Table 'mysql.innodb_table_stats' doesn't exist



그래도 에러가 난다면.



mysql 접속.


ELLORDNET-MPR:bin ellord$ ./mysql -u root -p mysql

Enter password: 



아래 5개 테이블 삭제.


innodb_index_stats
innodb_table_stats
slave_master_info
slave_relay_log_info
slave_worker_info


mysql> show tables;

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

| Tables_in_mysql           |

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

| columns_priv              |

| db                        |

| engine_cost               |

| event                     |

| func                      |

| general_log               |

| gtid_executed             |

| help_category             |

| help_keyword              |

| help_relation             |

| help_topic                |

| innodb_index_stats        |

| innodb_table_stats        |

| ndb_binlog_index          |

| plugin                    |

| proc                      |

| procs_priv                |

| proxies_priv              |

| server_cost               |

| servers                   |

| slave_master_info         |

| slave_relay_log_info      |

| slave_worker_info         |

| slow_log                  |

| tables_priv               |

| time_zone                 |

| time_zone_leap_second     |

| time_zone_name            |

| time_zone_transition      |

| time_zone_transition_type |

| user                      |

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

31 rows in set (0.01 sec)


mysql> drop table innodb_index_stats;



아래 5개 파일도 삭제.

innodb_index_stats.ibd
innodb_table_stats.ibd
slave_master_info.ibd
slave_relay_log_info.ibd
slave_worker_info.ibd

ELLORDNET-MPR:mysql ellord$ pwd

/Applications/MAMP/db/mysql56/mysql

ELLORDNET-MPR:mysql ellord$ rm -rf innodb_index_stats.ibd



5개 테이블 새로 만듬.


mysql> 


CREATE TABLE `innodb_index_stats` (

  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `stat_value` bigint(20) unsigned NOT NULL,

  `sample_size` bigint(20) unsigned DEFAULT NULL,

  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,

  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;


CREATE TABLE `innodb_table_stats` (

  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `n_rows` bigint(20) unsigned NOT NULL,

  `clustered_index_size` bigint(20) unsigned NOT NULL,

  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,

  PRIMARY KEY (`database_name`,`table_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;


CREATE TABLE `slave_master_info` (

  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',

  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',

  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',

  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',

  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',

  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',

  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',

  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',

  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',

  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',

  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',

  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',

  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',

  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',

  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',

  `Heartbeat` float NOT NULL,

  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',

  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',

  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',

  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',

  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',

  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',

  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',

  PRIMARY KEY (`Host`,`Port`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';


CREATE TABLE `slave_relay_log_info` (

  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',

  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',

  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',

  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',

  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',

  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',

  `Number_of_workers` int(10) unsigned NOT NULL,

  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',

  PRIMARY KEY (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';


CREATE TABLE `slave_worker_info` (

  `Id` int(10) unsigned NOT NULL,

  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  `Relay_log_pos` bigint(20) unsigned NOT NULL,

  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  `Master_log_pos` bigint(20) unsigned NOT NULL,

  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,

  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,

  `Checkpoint_seqno` int(10) unsigned NOT NULL,

  `Checkpoint_group_size` int(10) unsigned NOT NULL,

  `Checkpoint_group_bitmap` blob NOT NULL,

  PRIMARY KEY (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';






이제 다시 mysql_upgrade 를 시도해 본다.


ELLORDNET-MPR:bin ellord$ ./mysql_upgrade --user=root --password

Enter password:

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


sys.sys_config                                     OK

Upgrade process completed successfully.

Checking if update is needed.



위와 같이 정상적으로 업그레이드 되었다.


접속해 본다.

ELLORDNET-MPR:bin ellord$ ./mysql -u root -p mysql

Enter password: 

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A


Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.7.16 MySQL Community Server (GPL)


Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> 

mysql> select @@version;

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

| @@version |

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

| 5.7.16    |

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

1 row in set (0.00 sec)


mysql> 




5.7.16 으로 업데이트 되었다.

















블로그 이미지

엘로드넷

,