LingYanSi commented 8 years ago

mysql使用

增删改查🐶

有个教程

安装

brew install mysql mysql --version / mysql --help 校验是否安装成功

启动

mysql.server start / mysql.server stop

进入服务器

本地服务器

mysql -h127.0.0.1 -uroot -p123

-h host 127.0.0.1 localhost -u username 默认是root -p password 默认是123

远程服务器

mysql -h127.0.0.1 -P3306 -uusername -ppassword

-h : host ip地址 -P : Port 端口地址 -u : username 用户名 -p : password 密码

退出mysql

quit; / ctrl+d

查询所有的库

show databases;

新建一个数据库

create database name;

删除一个数据库

drop database name;

查库中所有的表

use database; show tables;

创建一个表

create table name
(
    姓名 char(8) not null,
    年龄 int unsigned not null,
    婚否 int unsigned not null
);

删除一个表

drop table name;

插入到表中

insert into tablename("周恩来",20,0);

从表中查询

select \* from tablename;
select 姓名,年龄 from tablename where 年龄>10;
select 姓名,年龄 from tablename where 年龄>10 or name like "%宋%";
select 姓名,年龄 from tablename where 年龄>10 or name like "%宋%" and 婚否=0;

and的优先级要比or高 where 子句不仅仅支持 "where 列名 = 值" 这种名等于值的查询形式, 对一般的比较运算的运算符都是支持的, 例如 =、>、<、>=、<、!= 以及一些扩展运算符 is [not] null、in、like 等等。还可以对查询条件使用 or 和 and 进行组合查询, 以后还会学到更加高级的条件查询方式, 这里不再多做介绍。

从表中删除
delete from 表名称 where 删除条件;

DELETE FROM sync_logs where id<1100000; DELETE FROM logs WHERE created_at<'2021-8-16 00:00:00';


### 更新表中数据
```sql
update 表名称 set 列名称=新值 where 更新条件;

重命名表

alter table 表名 rename 新表名;

删除列

alter table 表名 drop 列名称;

修改列

alter table 表名 change 列名称 列新名称 新数据类型;

添加列

alter table 表名 add 列名 列数据类型 [after 插入位置];

替换字符

update 表名 set 字段名=REPLACE(字段名,'currentString', 'targetString') where id>=0;

使用node-mysql

var sql = require('node-mysql')

const config = {
    which:{
        host: '1111', // 主机ip
        user: '11', // 用户名
        password: '1111', // 密码
        database: 'realtime', // 库名
        port: 3306  // 端口
    }
}

function mySql(name){
    // name用来对应不同数据库配置

    var self = this
    this.config = config[name]

    this.query = function(sql){
        return new Promise(function(resolve, reject){
            // 去查询
            self.connect.query(sql , function(error, row){
                if(error){
                    // console.log('查询错误', error)
                    reject(error)
                }else{
                    // 返回查询结果
                    resolve( row)
                }
            })

            // 因为mysql会有链接限制，因此在查询结束的时候，应该关闭链接
            // 或者使用 createPool，但这里有个蛋疼之处，每一次查询都是 new mySql()
            // 这样会导致，每次查询结束还是要手动关闭connect
            // 我想createPool的作用，在于，在启动服务器的时候就createPool
            // 如果想要查询，直接调用便是，不要每次都新建一个查询对象
            setTimeout(function(){ self.connect.distory() }, 1000)
        })

    }

}

mySql.prototype = {
    init: function(){
        var self = this

        this.connect = sql.createConnect( this.config )
        // If you using the node-mysql module, just remove the .connect and .end. Just solved the problem myself. Apparently they pushed in unnecessary code in their last iteration that is also bugged. You don't need to connect if you have already ran the createConnection call
        // 监听错误
        this.connect.on('error', function(){
            // 如果连接丢失，从新连接
            console.log('db error', err);
            if(err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection to the MySQL server is usually
              self.init();                                // lost due to either server restart, or a
            } else {                                      // connnection idle timeout (the wait_timeout
              throw err;                                  // server variable configures this)
            }
        })
    }
}

显示表信息

show full columns from article

desc 表名称

倒序查找

默认是正序查找

select * from packlist ORDER BY id DESC;

取前几条

select * from packlist ORDER BY id DESC LIMIT 10;

查看字符集，显示如下

SHOW VARIABLES LIKE "character%";

LingYanSi commented 8 years ago

windows安装

教程

下载zip包，解压打E:\mysql
以管理员身份打开一个cmd
cd bin
安装：mysqld -install / mysql -remove
启动：net start mysql / net stop mysql
登陆：mysql -u root

设置Path

win10搜索环境变量
在path中新增一个E:\mysql\bin

配置my.ini

#新版不支持在my.ini中直接设置字符集为utf8。解决方法是在default-character-set前面加上loose-。
loose-default-character-set = utf8

#加loose-后MySQL启动是不再报错了，但是在插入数据时依然出现了乱码问题。解决方法是加入character-set-server。
character-set-server = utf8

#如果是服务器用的话，建议设大点。
innodb_buffer_pool_size = 128M

#基路径
basedir = E:\mysql\

#数据路径
datadir = E:\mysql\data

#日志路径
log_bin = E:\mysql\log

#如果不加这行，默认是监听127.0.0.0，加了后是监听局域网端口和外网端口。
bind-address = 0.0.0.0

#监听端口一定要是3306
port = 3306

server_id = 1

E:\mysql\data 下有一个[用户名].err的错误日志文件，如果遇到什么错误，可以看下这个文件

LingYanSi commented 8 years ago

create table article(
    author char(30) NOT NULL, 
    content text NOT NULL,
    tags text NOT NULL,
    create_time bigint NOT NULL,
    update_time bigint NOT NULL,
    scan_num int(11),
    summary text,
    title char(255) NOT NULL,
    comments_num int(11),
    id int(11) AUTO_INCREMENT, 
    PRIMARY KEY ( id )
);

insert into article values("王刚", "我是内容", "我是标签", 12345679, 2222222);

如何设置一个自增id

 id int(11) AUTO_INCREMENT, 
    PRIMARY KEY ( id )

LingYanSi commented 8 years ago

CentOS安装

采用yum源安装，yum install mysql mysql-sever mysql-client很多教程上都这么写，实际上也是对的，对于大多数centos系统默认源来讲，确实如此。但是在阿里云服务器上，默认是阿里云自己配置的yum源，所以上述方法就会出问题，因为人家的yum源里面根本就没有这些东西。可能是包名改了吧，反正不是我们熟悉的那些。所以可以使用wget命令从官网上直接下载，就有了这么一种方法。 wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm 从官网上下载后会发现本地路径已经有了rpm包。这时候利用rpm命令将rpm包导入到yum源里面去 rpm -ivh mysql-community-release-el7-5.noarch.rpm 然后利yum install mysql-community-server.x86_64命令安装一路狂奔，安装好后使用mysql，还是不可以，因为mysqld服务没有开启。这时候使用service mysqld start然后安装结束，mysql正常开启。使用mysql直接进入数据库里面，但是这样一来不是都可以进去了？，这时候我们要做的就是加buff保护，也就是给数据库设密码。

mysqladmin -u root password "newpass" 如果root已经设置过密码，采用如下方法 mysqladmin -u root password oldpass "newpass"

完成后，若已经设置了密码，需要输入原密码修改，若未设置密码，那么上一步完成后就修改完成登录的时候也不能使用mysql进入了，估计也进不去，因为有暗号了，哈哈哈这个时候使用mysql -u root/你的密码就可以进去了。到此安装就说完了。

来源

设置编码

vim /etc/my.cnf 新增参数

[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8

[mysql]
default-character-set=utf8

检查编码

登录MySQL查看字符集

SHOW VARIABLES LIKE "character%";

Variable_name	Value
character_set_client	utf8
character_set_connection	utf8
character_set_database	utf8
character_set_filesystem	binary
character_set_results	utf8
character_set_server	utf8
character_set_system	utf8
character_sets_dir	/usr/share/mysql/charsets/

查看数据库编码信息 show table status; Collation = utf8_general_ci 方才正常

启动

service mysql start/stop/restart/reload

登录/操作与其他平台相同

LingYanSi commented 8 years ago

远程连接

参考文档1 参考文档2

# 使用root登录mysql
use mysql;

# 创建root用户 %表示不对ip进行限制
CREATE USER 'root'@'%' IDENTIFIED BY '你的密码';

# 给root用户所有的mysql权限
grant all privileges on *.* to 'root'@'%' with grant option;

#  刷新权限
FLUSH PRIVILEGES;

# 查看mysql用户信息
SELECT User, Host, Password FROM user;

# 远程登录
mysql -uroot -h服务器ip -p

[mysqld]
#

# \* Basic Settings

#
default-character-set=utf8
default-storage-engine=INNODBuser            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-external-lockinggrant all on sonardb.\* to sonar@’%’ identified by ‘123456′;
grant all on sonardb.\* to sonar@localhost identified by ‘123456′;
sonardb替换为你想访问的数据库名,sonar是你的想使用的用户名,123456替换为你的密码,这样就开启了远程访问功能.

bind-address    = 192.168.1.107

LingYanSi commented 8 years ago

使用emoji

emoji来源于日语，表示字符的意思

utf8编码只支持1-3个字节，而一个emoji需要4个字节，因此需要对编码进行修改

停止mysql service mysql stop
修改配置文件

# utf-8
[client]
default-character-set=utf8mb4

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# 内存较小机器可以使用此参数
performance_schema=OFF

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 character-set-client-handshake = FALSE
 character-set-server = utf8mb4
 collation-server = utf8mb4_unicode_ci
 init_connect='SET NAMES utf8mb4'

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# utf-8

[mysql]
default-character-set=utf8mb4

重修启动mysql service mysql start
查看下表信息数据库字符集

SHOW VARIABLES LIKE "character%";

查看表信息

show create table table_name;

LingYanSi commented 8 years ago

修改数据库字符集：

ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];

把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：

ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]
如：ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

只是修改表的默认字符集：change default charset uft8 -> utf8mb4

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];
如：ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

修改字段的字符集：

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

查看数据库编码：

SHOW CREATE DATABASE db_name;

查看表编码：

SHOW CREATE TABLE tbl_name;

查看字段编码：

SHOW FULL COLUMNS FROM tbl_name;

LingYanSi commented 7 years ago

服务器定时备份与恢复

一开始的时候，不知道怎么给数据库设置密码，然后就被黑了一次😅 后来虽说设置了密码，但是为了数据的安全性，还是应该做好数据的备份容灾

备份命令

mysqldump -uroot -p db_name > beifen1.sql

以上，如果在命令行操作，没什么问题。不过，在shell脚本中就麻烦了，因为要手动书输入密码。

命令自动输入密码

mysql -uroot -ppassword db_name < beifen1.sql

当然还可以加上自动上传功能，把备份文件也存到其他机器上

curl

每隔30分钟备份一次数据

#! /bin/bash
while (true) {
    Now=$(date +"%y-%m-%d--%H:%M:%S")
    File=backup-$Now.sql 
    mysqldump -uroot -h127.0.0.1 -p$password db_name >  $File.sql
    echo "数据备份成功$(File).sql"
    sleep("30m")
}

crontab -e

配置定时任务 http://www.cnblogs.com/mingforyou/p/3930636.html

*       *         *     *     ls             指定每小时的第5分钟执行一次ls命令
5       *         *     *     ls             指定每天的 5:30 执行ls命令 
7       8         *     *     ls             指定每月8号的7：30分执行ls命令
5       8         6     *     ls             指定每年的6月8日5：30执行ls命令 
6       *         *     0     ls             指定每星期日的6:30执行ls命令[注：0表示星期天，1表示星期1， 以此类推， 也可以用英文来表示，sun表示星期天，mon表示星期一等。]
3       10,20     *     *     ls             每月10号及20号的3：30执行ls命令[注：“，”用来连接多个不连续的时段]
8-11    *         *     *     ls             每天8-11点的第25分钟执行ls命令[注：“-”用来连接连续的时段]

*/15    *         *     *     ls             每15分钟执行一次ls命令 [即每个小时的第0 15 30 45 60分钟执行ls命令 ]
6       */10      *     *     ls             每个月中，每隔10天6:30执行一次ls命令[即每月的1、11、21、31日是的6：30执行一次ls 命令。 ]

crontab 对应文件必须要有执行权限

chmod+x /absolute/file/path.name

添加命令

* */1 * * * /absolute/file/path.name > /log/xx.log

恢复

先新建一个数据库

create database fuck

再恢复

mysql -u root -h 127.0.0.1 -p databaseName < backup.sql

从远端cp到本地

ssh-copy root@1.24.2.1:/root/beifen1.sql ~/dir/path

LingYanSi commented 7 years ago

设置密码允许远程访问

use mysql;

# 设置密码
UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';
# 更新权限
flush privileges;

# 允许远程访问
update user set host = '%' where user = 'root';
flush privileges;

这样之后，在服务器登录mysql需要

mysql -u root -h 127.0.0.1 -p

LingYanSi commented 7 years ago

mysql自动关闭

mysql默认占用的内存一般在500M甚至以上，而自己使用的腾讯云服务器内存只有1G，正常情况下的剩余内存只有

解决方案: 在my.cnf中添加performance_schema=OFF可解决 MySQL 内存占用过高分析

可以看到每个容器的内存占用，看了真的吓一跳，MySQL 竟然占用了近500M的内存，而其他的基本就是几十兆。奇怪的是，我服务器上也安装了 MySQL，就没有这么大的内存占用。后来经过搜索，看到这个文章 https://mariadb.com/blog/starting-mysql-low-memory-virtual-machines，说关闭performance_schema可以减少大量内存占用，我试了一下，果然是这样。再一查，这个选项是在 MySQL5.6中开始被默认启用的，我服务器上装的是5.5，docker 容器里面的是5.7。

在 http://bugs.mysql.com/bug.php?id=69665 也有相关的讨论，使用show engine performance_schema status看了下，果然它占用了绝大部分的内存。

在 my.cnf 中设置performance_schema=OFF后重新启动 MySQL，发现问题解决，内存占用为几十兆左右。

LingYanSi commented 5 years ago

使用 mariadb 替换mysql

mariadb简介MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语：XtraDB）来代替MySQL的InnoDB。 MariaDB由MySQL的创始人Michael Widenius（英语：Michael Widenius）主导开发，他早前曾以10亿美元的价格，将自己创建的公司MySQL AB卖给了SUN，此后，随着SUN被甲骨文收购，MySQL的所有权也落入Oracle的手中。MariaDB名称来自Michael Widenius的女儿Maria的名字。

在centos7.0后,系统默认使用mariabd代替mysql.

yum install mariadb mariadb-server

systemctl start mariadb  #启动MariaDB
systemctl stop mariadb  #停止MariaDB
systemctl restart mariadb  #重启MariaDB
systemctl enable mariadb  #设置开机启动

# 修改root的密码
update mysql.user set password=PASSWORD('yhb123456') where user='root';
# 更新权限
flush privileges;

# create user  '用户名'@'主机' identified by '密码'
# 如果只允许本机访问 @'localhost'
# 或者指定一个ip  @'192.xx.xx.xx' 或者使用通配: @'%'
create user 'read_visa'@'%' identified by '123456';

# grant 操作类型 on 数据库.表 to 用户@'主机'
# 数据库,表,主机都支持通配符 grant select, insert on *.* to  'read_visa'@'%'
grant all on visa.* to 'read_visa'@'%'; # all 表示所有权限
grant select on visa.* to 'read_visa'@'%';

使用emoji

解决方案新增文件 /etc/my.cnf.d/character-set.cnf

# https://scottlinux.com/2017/03/04/mysql-mariadb-set-character-set-and-collation-to-utf8/
# https://mariadb.com/kb/en/library/setting-character-sets-and-collations/
# https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e434
# https://stackoverflow.com/questions/47566730/force-mariadb-clients-to-use-utf8mb4

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci
init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4

MariaDB [(none)]> show variables like 'collation%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb4_unicode_ci | | collation_database | utf8mb4_unicode_ci | | collation_server | utf8mb4_unicode_ci | +----------------------+--------------------+ 3 rows in set (0.001 sec)

LingYanSi commented 2 years ago

设置数据库密码

To change the root password, type the following at the MySQL/MariaDB command prompt: ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyN3wP4ssw0rd'; flush privileges; exit;

use mysql
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyN3wP4ssw0rd'
flush privileges

1.以root身份在终端登陆(必须) 2.输入 mysqladmin -u root -p password ex 后面的 ex 是要设置的密码 3.回车后出现 Enter password 输入就密码，如果没有，直接回车

for mariadb

https://mariadb.com/kb/en/set-password/

# 登录mysql
sudo mysql
# 切换到mysql这个database上去
use mysql;
# 设置密码
SET PASSWORD FOR 'bob'@localhost = PASSWORD("");

LingYanSi commented 2 years ago

sql文件执行

方法一 mysql -h localhost -u root -p123456 < path/to/file.sql
方法二登录mysql之后执行 source path/to/file.sql

LingYanSi / blog

mysql #25

mysql使用

增删改查🐶

安装

启动

进入服务器

本地服务器

远程服务器

退出mysql

查询所有的库

新建一个数据库

删除一个数据库

查库中所有的表

创建一个表

删除一个表

插入到表中

从表中查询

从表中删除

重命名表

删除列

修改列

添加列

替换字符

使用node-mysql

显示表信息

倒序查找

windows安装

CentOS安装

设置编码

检查编码

启动

登录/操作 与其他平台相同

远程连接

使用emoji

服务器定时备份与恢复

crontab -e

恢复

设置密码 允许远程访问

mysql自动关闭

使用 mariadb 替换mysql

使用emoji

设置数据库密码

for mariadb

sql文件执行

登录/操作与其他平台相同

设置密码允许远程访问