jack-zheng/MySQL Installation.md

MySQL Installation

Documents

Steps of MacOS

command brew list and brew services list to check if already installed
brew intall mysql to install, after this mysql is auto up
run script mysql_secure_installation to setup password and security etc, attached sample below
mysql -u root -p to login

mysqldump -uroot -p --databases test > test.sql 导出数据
drop database <数据库名>; 删除数据库
mysql -uroot -p < test.sql 导入数据
mysql.server stop/start or brew services start/stop mysql to manage mysql, brew command is prefered

Steps of Docker

docker pull mysql
docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=12345678 -d mysql
docker exec -it mysql bash
mysql -u root -p

Connect from local machine: mysql -h localhost -P 3306 --protocol=tcp -u root -p
-it, i: interactive; t: pseudo-TTY
docker exec -i mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < test.sql 导入数据到 container

Use volume to persistent data

command docker inspect mysql check 'Mounts' info to get the destination, e.g. "Destination": "/var/lib/mysql"
create volume for testing, docker volume create mysqldata
docker volume list and docker inspect mysqldata to show volume info
create container and link to this volumn docker run --name mysql02 -p 3308:3306 --mount source=mysqldata,destination=/var/lib/mysql -e MYSQL_ROOT_PASSWORD=12345678 -d mysql
backup volume docker run -v mysqldata:/volume --rm loomchild/volume-backup backup - > databackup.tar.bz2, 'loomchild/volume-backup' is a temp image to dump volume
restore volume cat databackup.tar.bz2 | docker run -i -v backuptest:/volume --rm loomchild/volume-backup restore -, 'backuptest' is the volume name you want

volume 是数据区，但是同一时间只能有一个 container 连到上面，多个 container 链接时会报错 ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0。不过多个 app container 链接到 mysql container 应该是能办到的。

好像通过 volume driver 可以办到上面说的事，没试过，用到再说。

--volume , -v Bind mount a volume
--rm Automatically remove the container when it exits

Sample of sercuring script selections

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file
// 这里提示选一个密码强度等级
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
Please set the password for root here.
// 然后按照所选的密码强度要求设定密码
New password:

Re-enter new password:

Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
 ... Failed! Error: Your password does not satisfy the current policy requirements

New password:

Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
// 这里删除默认无密码用户
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
// 禁止远程root登录，我选的是不禁止。因为我的mac上的数据库不会放到公网上，也不会存什么敏感数据
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : no

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

// 这里删除默认自带的test数据库
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

Sample of SQL file

-- MySQL dump 10.13  Distrib 8.0.16, for osx10.14 (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	8.0.16

/*!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 */;
 SET NAMES utf8mb4 ;
/*!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 */;

--
-- Current Database: `test`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `test`;

--
-- Table structure for table `runoob_tbl`
--

DROP TABLE IF EXISTS `runoob_tbl`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 SET character_set_client = utf8mb4 ;
CREATE TABLE `runoob_tbl` (
  `runoob_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `runoob_title` varchar(100) NOT NULL,
  `runoob_author` varchar(40) NOT NULL,
  `submission_date` date DEFAULT NULL,
  PRIMARY KEY (`runoob_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `runoob_tbl`
--

LOCK TABLES `runoob_tbl` WRITE;
/*!40000 ALTER TABLE `runoob_tbl` DISABLE KEYS */;
INSERT INTO `runoob_tbl` VALUES (1,'学习 PHP','菜鸟教程','2019-10-24'),(2,'学习 MySQL','菜鸟教程','2019-10-24'),(3,'JAVA 教程','RUNOOB.COM','2016-05-06');
/*!40000 ALTER TABLE `runoob_tbl` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-10-24 14:24:51