[MySQL] 升級 8 之後…

原本 mysql 8 之前，我都會用  'GRANT' 指令來新增使用者管理某個資料庫權限，升級後，這個動作失敗了，因為得先有 user，才能賦予權限。

dbname

username

MYPASSWORD

GRANT ALL PRIVILEGES on dbname.* TO username@localhost IDENTIFIED BY 'MYPASSWORD';

change to:

CREATE USER username@localhost IDENTIFIED BY 'MYPASSWORD';

GRANT ALL PRIVILEGES on dbname.* TO username@localhost ;

清除MySQL 的 bin log

參考：迷失露的迷失 - MySQL 的 bin log 太多如何清除？
-------------------------------------------------------------------

千萬不要這麼做
# rm -rf mysql-bin.000001  (X, 錯誤做法)

你應該由 mysql 去控制／刪除這些檔
刪除單一檔的指令是 ： PURGE BINARY LOGS TO 'mysql-bin.000001’;

刪除 7 天前的檔案指令是：PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY) + INTERVAL 0 SECOND;

自動刪除7天前的 log，那麼請在你的 my.cnf 加入 expire-logs-days=7

沒有replication，建議可以取消 log-bin
#log-bin=mysql-bin 

轉：DOCKER – HOW TO CONNECT TO A MYSQL RUNNING CONTAINER

轉貼自 LITTLETECHBLOGGER
---------------------------------

1. What we want to achieve:

We used the official mysql image from Docker Hub (https://hub.docker.com/_/mysql/), and created a running database container.

Now we need to connect to the MySQL database using MySQL command line client, to do some database operation.

2. Solution1:

Bash into the running container and run MySQL client.

2.1 Bash into the running MySQL container:

$ docker exec -t -i <container_name> /bin/bash

2.2 Run MySQL client:

$ mysql -u “<useranme>” -p

3. Solution2:

Use MySQl Command Line Client.

Reference: MySQL Image Official Doc

In the official guide, it states:

The following command starts another mysql container instance and runs the mysql command line client against your original mysql container, allowing you to execute SQL statements against your database instance:

$ docker run -it --link some-mysql:mysql --rm mysql sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"'

The variables here are really confusing.  Here is a more readable version of this command:

$ docker run -it --link <container_name>:<image_name> --rm <image_name> sh -c 'exec mysql -h"<container_ip_address>" -P"<mysql_port_number>" -u"<mysql_username>" -p"<mysql_password>"'

Now, lets use an example to explain how to find all the variables.

3.1 Get container_name, image_name, mysql_port_number:

$ docker ps

We can get:

container_name: romuiweb_db_1

image_name: mysql

container_port_number: 3306

3.2 Get container_ip_address:

$ docker inspect –format ‘{{ .NetworkSettings.IPAddress }}’ <container_id>

3.3 Get mysql_username, mysql_password:

When we start the mysql image, we set environment variables to decide the username and password.

username is default to root.

password is set by MYSQL_ROOT_PASSWORD, and we can find it in docker-compose.yml file:

So we can get:

mysql_username: root,

mysql_password: password.

3.4 Create the real command:

Now we have all the values of the variables. We can construct the real command:

$ docker run -it –link romuiweb_db_1:mysql –rm mysql sh -c ‘exec mysql -h”172.17.0.2″ -P”3306″ -u”root” -p”password”‘

3.5 Run the command, gain access to MySQL:

If it works, skip the following problem solving part.

3.6 Problem Solving:

(In this problem, we are not using romuiweb_db_1 we used earlier. We switch to docker_db_1. The Ip Address is different now)

docker: Error reponse from daemon: Cannot link to <container_name>, as it does not belong to the default work.

$ docker run -it –link docker_db_1:mysql –rm mysql sh -c ‘exec mysql -h”172.20.0.3″ -P”3306″ -u”root” -p”password”‘

This means that we are not running the mysql connect container on the same network the mysql container is currently running on. We need to connect it to the same network use –net option. Reference: https://docs.docker.com/engine/userguide/containers/networkingcontainers/

Step1: Find out which network mysql container is running on:

$ docker inspect docker_db_1

Find the “Networks”:

As we can see from the screenshot above, the network docker_db_1 is running on is “docker_default”. We need to connect to this network. So we change out command to this:

$ docker run -it –net=docker_default –link docker_db_1:mysql –rm mysql sh -c ‘exec mysql -h”172.20.0.3″ -P”3306″ -u”root” -p”password”‘

After adding the network argument, we successfully connected:

[mysql] 升級到 MySQL 5.7 時，突然無法新增資料

mysql 若有從 5.x 升級到 5.7 的夥伴注意一下，date, datetime 在 5.7 的版本預設是不允許空白或 '0000-00-00" 、"0000-00-00 00:00:00" 的內容，所以若要新增時會發生錯誤！

可以在 my.cnf 把 sql_mode 修改如下：

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION" 

或是臨時解決方式：
在每一次新增時，先執行這段指令，也可以避開這個錯誤！

SET sql-mod='';

[MySQL] MySQL預設編碼

指令：

SET NAMES 'utf8';
SET CHARACTER SET utf8;

設定檔 my.cnf：

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

[MySQL] 升級 MySQL 5.7 後，資料庫會出現錯誤訊息

錯誤訊息：

1577 - Cannot proceed because system tables used by Event Scheduler were found damaged at server start

解決辦法1. 移除 mysql 資料庫中的資料庫

•  innodb_index_stats
•  innodb_table_stats
•  slave_master_info
•  slave_relay_log_info
•  slave_worker_info

2. 在磁碟中找到 步驟1 中相同資料表名的 *.frm 和 *.ibd 的檔案

3. 利用下列的查詢語法來建立資料表

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';

4. 重新啟動 mysql server