![](https://img-blog.csdnimg.cn/20201014180756922.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
mysql
弘宇的IT屋
千里之行,始于足下
展开
-
mysql常见问题汇总
1.指定用户访问指定数据库grant all privileges on imserver.* to 'imserver'@'%' identified by '1204';flush privileges;原创 2020-09-09 10:36:14 · 264 阅读 · 0 评论 -
Slave failed to initialize relay log info structure from the repository
mysql主从配置之后,重启后出现Slave failed to initialize relay log info structure from the repository解决方法:在start slave之前,先启用reset slave原创 2020-09-09 10:23:07 · 5128 阅读 · 1 评论 -
springboot mysql实现读写分离
mysql主从设置参考:mysql主从设置1.pom设置spring: data: master: driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://192.168.1.47:3306/im-block?autoReconnect=true&useUnicode=true&allowMultiQueries=true&am.原创 2020-08-14 11:34:53 · 259 阅读 · 0 评论 -
springboot整合mysql mybatis tkmybatis
1.配置pom,引入mysql mybatistkmybatis线程池和分页的依赖 <properties> <mybatis-version>2.1.2</mybatis-version> <mysql-version>5.1.46</mysql-version> <alibaba-version>1.1.22</alibaba-version> <pagehelper-version&g...原创 2020-08-01 14:24:17 · 343 阅读 · 0 评论 -
ubuntu16 mysql实现主从备份
1.环境选择 1.1ubuntu16.04 1.2 mysql 5.71.2 设置mysql环境 修改/etc/mysql/mysql.conf.d/mysqld.cnf 打开binlogserver-id = 1log_bin = /var/log/mysql/mysql-bin.log 重启mysql/etc/init.d/mysql restart1.3master指定slave账号CREATE USER 'sl...原创 2020-06-09 18:03:05 · 211 阅读 · 0 评论 -
mysql this is incompatible with sql_mode=only_full_group_by
解决方案,修改mysqlmy.cnf/重启就可以sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION原创 2020-01-08 11:26:59 · 156 阅读 · 0 评论 -
mysql恢复和回滚数据
数据库不小心删除或者表不小心删除,通过mysql恢复的话需要确保删除前是mysql是开启binlog。具体步骤:1.查询binlog状态以及位置。在/etc/my.cfg查看binlog开启状态:cat /etc/my.cnf可以看到binlog开始状态是开启的。2.mysql查询执行的binlog文件。目标文件是mysql-bin.0000533.利用...原创 2019-09-23 14:31:13 · 896 阅读 · 0 评论 -
mysql备份并zip加密压缩
currentpath=/backup/sqlbackpath="test"zipbackuptime=30zippasswd="password"zippath=/work/zipfunction makedir(){ echo $(date "+%Y-%m-%d %H:%M:%S") if [ -d $currentpath/$(date +%y%m%d) ]; t...原创 2019-09-02 10:54:15 · 836 阅读 · 0 评论 -
ERROR 2006 (HY000) at line : MySQL server has gone away
mysql -uroot -p1204 aibase <base.sql出现ERROR 2006 (HY000) at line : MySQL server has gone away修改my.cnfmax_allowed_packet = 16M大小写不区分lower_case_table_names = 1原创 2019-07-10 20:09:59 · 1396 阅读 · 0 评论 -
mysql设置自增长ID初始值
alter TABLE superuser auto_increment =1原创 2019-04-26 13:15:30 · 3001 阅读 · 0 评论 -
mysql修改密码
mysql修改密码的方式常见的有以下四种:1. 已知密码的情况下:1.1 mysqladmin修改mysqladmin -uroot -p1204 password 12341.2 set passwordset password for root@localhost = password('2345');1.3 update user (mysql5.7以后是set...原创 2019-03-11 20:52:18 · 333 阅读 · 0 评论 -
Ubuntu 定时备份指定mysql数据库
写个shell脚本配合crontab定时备份1. 创建备份目录,书写备份脚本sqlbackup.shmkdir/share/backupmkdir/share/backup/sql#!/bin/bashcurrentpath=/share/backup/sqlbackpath="testnet zentao"function makedir(){ echo ...原创 2019-03-11 14:41:58 · 272 阅读 · 0 评论 -
Ubuntu mysql导入/导出数据库
ubuntu导入/导出数据库常用的phpmyadmin,navicat等工具类和网页类。下面用命令导入/导出1. 导入: 1.1 外部命令导入mysql -uroot -ppasswdmysql->create database test;mysql->exit;mysql -uroot -ppasswd test < web/test.sql1...原创 2019-03-07 20:57:00 · 384 阅读 · 0 评论 -
mysql开放远程连接
LInux ubuntu上开放mysql远程连接,需要2个步骤:1.修改 /etc/mysql/my.cnf找到bind-address = 127.0.0.1注释掉或者修改成bind-address = 0.0.0.0 2.进入mysql修改远程权限mysql -uroot -ppasswd grant all privileges on *.* to 'root'@...原创 2019-02-20 21:29:19 · 538 阅读 · 0 评论 -
mysql远程连接出现 Host 'xxxxxxx' is blocked because of many connection errors; unblock with 'mysqlad
机器A通过终端命令连接机器B的数据库,在账号密码输错的情况下容易出现Host 'xxxxxxx' is blocked because of many connection errors; unblock with 'mysqlad。A远程连接B的命令如下: mysql -uroot -ppasswd -h192.168.1.105(B)解决方法:1.临时方法在B机器上登录...原创 2019-02-20 21:12:31 · 2847 阅读 · 0 评论 -
mysql查询数据表的存储引擎
数据表的存储引擎,在mysql中常用的查询方式有2种:1. 通过show create table `sys_user`; 可以查出是InnoDB2.show table status from bootdo where name='sys_user' \G...原创 2019-03-05 12:15:59 · 384 阅读 · 0 评论