mysql

create user 'canal'@'%' identified by 'Canal@123456';
flush privileges;

新建用户无法直接登录，报错

使用下面命令

ALTER USER 'canal'@'%' IDENTIFIED WITH mysql_native_password BY 'Canal@123456';

flush privileges;

授权

grant SELECT, REPLICATION SLAVE, REPLICATION CLIENT on *.* to 'canal'@'%' with grant option;

SHOW VARIABLES like 'log_bin'


SHOW BINARY logs;

SHOW MASTER status;

修改配置文件

docker exec -it 容器ID /bin/bash

cd /etc/mysql
apt-get update
apt-get install vim

vi my.cnf
[mysqld]
skip-name-resolve

创建用户

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

GRANT all privileges ON devdb.* TO 'target123'@'%';


ALTER USER 'target123'@'%' IDENTIFIED BY '1234' PASSWORD EXPIRE NEVER; 

ALTER USER 'target123'@'%' IDENTIFIED WITH mysql_native_password BY '1234';

FLUSH PRIVILEGES;

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'target123'@'%';  

备份与还原

备份的sql执行报错 解决办法

查看配置的大小
show global variables like 'max_allowed_packet';

修改大小
set global max_allowed_packet=1024*1024*150;

还原sql 命令

备份
mysqldump -u root -p[密码] --databases [库名] > [备份路径文件名].sql

还原
mysql -u[用户名] -p[密码] < [备份文件全路径].sql

mysqldump -uroot -p[密码] --databases 库名1 [库名2].. >/备份路径/备份文件名.sql#导出的就是数据库脚本文件
 
mysqldump -u root -p --databases school > /opt/mysql_bak/school.sql    #完全备份一个库school
mysqldump -u root -p --databases school market > /opt/mysql_bak/school-market.sql   #完全备份多个库，school库和market库

批量更新

修改yml 配置文件 添加 &allowMultiQueries=true

jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true

 <update id="batchUpdateDataSiDian">
        <foreach collection="list" item="histData" separator=";" close=";">
            UPDATE biz_surroundrock_data
            <set>
                <if test="histData.rightValue != null ">right_value = #{histData.rightValue},</if>
                <if test="histData.leftValue != null ">left_value = #{histData.leftValue},</if>
                <if test="histData.topValue != null">top_value = #{histData.topValue},</if>
                <if test="histData.downValue != null">down_value = #{histData.downValue},</if>
            </set>
            WHERE ID = #{histData.id}
        </foreach>
    </update>

修改密码

[mysql] 
default-character-set=utf8 
[mysqld] 
port=3306 
basedir =D:\KDZT\MySql
datadir =D:\KDZT\MySql\data
character-set-server=utf8
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# skip-grant-tables

update user set authentication_string = password("123456") where user = "root";

替换