Mysql

GitbookBackend2021-02-22

🪕 在产品机上的安装

阿里云
centOS7.2
MySQL5.5

# cd /home/downloads
# wget https://cdn.mysql.com//Downloads/MySQL-5.5/mysql-5.5.61-linux-glibc2.12-x86_64.tar.gz;
#tar vxzf mysql-5.5.61-linux-glibc2.12-x86_64.tar.gz;
# mv mysql-5.5.61-linux-glibc2.12-x86_64 mysql-5.5.61;

$ cd /home/mysql/mysql-5.5.61/bin/;
$ mysqld_safe --defaults-file=/home/mysql/my.cnf --user=mysql &
$ mysqladmin -u test password 'Test123' -S /home/mysql/3306_mysql.sock -hlocalhost -P3306
$ mysql -S /home/mysql/3306_mysql.sock -hlocalhost -P3306 -utest -p'Test123'

注意：生产环境推荐采用这种方式/home/mysql/mysql-5.5.61/bin/mysqlsecureinstallation 移除测试数据库和匿名用户。

📑 PORT

# In /etc/my.cnf
port=...
systemctl restart mysqld
netstat -ntlp

/usr/local/etc/my.cnf:

[mysqld]
# Only allow connections from localhost
bind-address=127.0.0.1
character_set_server=utf8
collation_server=utf8_general_ci
port=3306

📑 Sequelize

We’re going to use Sequelize, which is a database ORM that will interface with the Mysql database for us.

📑 Postman

For test RESTFul APIs.

📑 Mac

add the following to ~/.xxx_profile (.zsh, .bash etc)

export MYSQL_HOME=/usr/local/opt/mysql
alias start_mysql='sudo $MYSQL_HOME/bin/mysqld_safe &'
alias stop_mysql='sudo $MYSQL_HOME/bin/mysqladmin shutdown'

or, simplier:

mysql.server stop
mysql.server start
mysql.server restart

📑 授权

ALTER USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Test';

ALTER USER 'test' IDENTIFIED WITH mysql_native_password  BY 'Test'

mysql -u test -p"test" -h localhost  <<__EOF__

create DATABASE test;

create user user1@'localhost' identified by 'test123';

grant all privileges on test.* to user1@'localhost';
__EOF__


mysql -u user1 -p"test123"  -D"test" -h localhost  << ./bootstrap.sql

mysql> create database testdb;
mysql> create user 'user1'@'localhost' identified by 'test123';
mysql> grant all privileges on testdb to user1@’localhost’
mysql> grant all on *.* to user1@'localhost';
mysql> alter user user1@'localhost' identified with mysql_native_password by 'test123';
mysql> use testdb;
mysql> create table customers (customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name TEXT, last_name TEXT);

$ mysql -uroot

> create user test;
> ALTER USER 'test' IDENTIFIED WITH mysql_native_password  BY 'Test'
> create database test;
> use test;
> grant all privileges on *.* to 'test';

> update user set authentication_string=password('123456') where user='test' and Host = 'localhost';

CREATE TABLE `testdb`.`user` (
	`id` bigint(20) NOT NULL AUTO_INCREMENT  PRIMARY KEY,
	`username` varchar( 30 ) NOT NULL ,
	`email` varchar( 50 ) NOT NULL ,
	`password` varchar( 128 ) NOT NULL ,
	UNIQUE KEY `username` ( `username` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8;


CREATE TABLE `testdb`.`ROLE` (
	`id` bigint(20) NOT NULL AUTO_INCREMENT  PRIMARY KEY,
	`name` varchar(60) NOT NULL
) ENGINE = MYISAM DEFAULT CHARSET = utf8;

CREATE TABLE `testdb`.`User_ROLEs` (
	`user_id` bigint(20),
	`role_id` bigint(20)
) ENGINE = MYISAM DEFAULT CHARSET = utf8;

📑 MySQL port config

edit /etc/my.ini or /usr/local/etc/my.cnf, the content like this:

[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
character_set_server=utf8
collation_server=utf8_general_ci
port=3306

📑 MySQL

User.hasMany(Tweet, { as: "Tweets", foreignKey: "userId" });
Tweet.belongsTo(User, { as: "User", foreignKey: "userId" });

📑 port

$ lsof -n -P -i | grep 3306

📑 日常

ALTER TABLE `test`.`flows`
ADD CONSTRAINT `flows_ibfk_5` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE SET NULL ON UPDATE CASCADE

SET foreign_key_checks = 0
update table ...
SET foreign_key_checks = 1

$ sequelize model:generate --name Credit1 --attributes name:string
$ sequelize model:generate --name Credit2 --attributes name:string
$ sequelize model:generate --name Credit1Flow --attributes name:string
$ sequelize model:generate --name CirculationFlow --attributes name:string
$ sequelize model:generate --name FinancingFlow --attributes name:string

📑 授权法

例如：你想myuser使用mypassword（密码）从任何主机连接到 mysql 服务器的话。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器，并使用mypassword作为密码

mysql>GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3'IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

mysql>FLUSH PRIVILEGES

使修改生效，就可以了

📑 启动停止 MySQL

export MYSQL_HOME=/usr/local/opt/mysql
alias start_mysql='sudo $MYSQL_HOME/bin/mysqld_safe &'
alias stop_mysql='sudo $MYSQL_HOME/bin/mysqladmin shutdown'

📑 更改口令

> use mysql;
> update user set password=PASSWORD('Test123') where User='shanmeifactoring';
> FLUSH PRIVILEGES;

📑 Mysqldump

$ mysql -u shanmeifactoring -p -h 116.196.85.178

$ /usr/bin/mysqldump -u dzfactoring_user -p"ShanxidazongUser123!" -d shanmei | gzip > "/home/prod6/backup-$(date)" 2> dump.log

$ mysqldump --defaults-file="c:\users\willi\appdata\local\temp\tmpcxph8q.cnf"  --set-gtid-purged=OFF --user=shanmeifactoring --host=116.196.85.178 --protocol=tcp --port=3306 --default-character-set=utf8 --skip-triggers "test"

$ mysql.exe --defaults-file="c:\users\willi\appdata\local\temp\tmpg52f9y.cnf"  --protocol=tcp --host=116.196.85.178 --user=dev_user_general --port=3306 --default-character-set=utf8 --comments --database=test  < "C:\\Users\\willi\\Documents\\dumps\\Dump20200315-116.sql"

$ /usr/bin/mysql --user=test  --database=test --password=test --silent