MySQL 常见问题

作者: 33d31a1032df | 来源:发表于2017-01-26 23:49 被阅读24次

第六弹：MySQL常见问题处理
MySQL面试指南
centOs7中 mysql8.0 安装，配置，主从复制
SQL优化学习
MySQL主从库配置以及常见问题
软件技术--Mysql系列
尚硅谷大数据技术之Oozie
mysql常见问题
mysql常见问题
mysql 常见问题

官网：https://dev.mysql.com/doc/refman/5.6/en

安装

RPM安装（CentOS）

安装前，先删除已经安装的mysql、mariadb，并删除残留文件

rpm -aq | grep mysql
rpm -e --nodeps mysql
rpm -aq | grep mariadb-libs
rpm -e --nodeps mariadb-libs

rm -f /etc/my.cnf
rm -rf /var/lib/mysql
rm -rf /var/share/mysql
rm -rf /usr/bin/mysql

rpm -ivh mysql-server.rpm
rpm -ivh mysql-client.rpm

编译安装（CentOS）

# 安装编译工具
yum install gcc gcc-c++ cmake 

# 安装依赖
yum install perl perl-Module-Install.noarch
yum install ncurses-devel

# 添加用户
groupadd mysql
useradd -g mysql mysql

# 解压源码包
tar –xzvf mysql.tar.gz -C /usr

# 编译安装
cd /usr/mysql
cmake -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DCMAKE_INSTALL_PREFIX=/usr/mysql -DMYSQL_DATADIR=/var/mysql
make && make install
scripts/mysql_install_db --user=mysql --datadir=/var/mysql --pid-file=/var/mysql/mysql.pid

# 注册系统服务
ln -s /usr/mysql/support-files/mysql.server /etc/init.d/mysql

# 开机启动
chkconfig mysql on

# 添加环境变量
vi /root/.bash_profile
--------------------[    update   ]--------------------
MYSQL_HOME=/usr/mysql
PATH=$PATH:$MYSQL_HOME/bin
-------------------------------------------------------
source /root/.bash_profile

解压安装（Window）

解压文件，控制台启动

bin\mysqld --console

修改初始密码

# 查看初始密码
cat /root/.mysql_secret
# 使用客户端登录
mysql -uroot -pMYSQL_SECRET

-- 修改密码
set password=Password('123456');

启动/关闭/重启/登录

sudo service mysql start
sudo service mysql stop
sudo service mysql restart
mysql -uroot -p

备份还原

-h 可选，需要备份的数据库地址
-u 用户名
-p 密码，可以不直接输入

# 整库备份，数据库名test，备份文件名test.sql
mysqldump -h192.168.1.191 -uroot -p123456 test > test.sql

# 特定表备份，表名user、area、post
mysqldump -uroot -p123456 test user area post > test.sql

# 只导出表结构，不包括数据
mysqldump -uroot -p123456 -d test > test.sql

# 只导出表数据，不包括结构
mysqldump -uroot -p123456 -t test > test.sql

# 导出表结构、存储过程、自定义函数
mysqldump -uroot -p123456 -d -R test > test.sql

# 还原
mysql -h192.168.1.191 -uroot -p123456 test < test.sql

常用操作

-- 删除无效用户
use mysql;
select host, user from user;
delete from user where user='';

-- 创建全权限用户
create user admin;
grant all privileges on *.* to 'admin'@'%' identified by '123456' with grant option;
flush privileges;

-- 创建数据库
create database if not exists test default character set utf8;
drop database if exists test;

-- 重命名数据库
rename database test to test_bak;

-- 创建表
use test;
create table if not exists user(
    id bigint primary key auto_increment,
    account varchar(100),
    password varchar(100),
    unique index unique_index_account(account)
);

-- 删除表
drop table if exists user;

-- 修改表
alter table user add column age int;
alter table user drop column age;
alter table user modify column account varchar(200);
alter table user change account username varchar(200);
alter table user add index index_account(account); 

-- 查状态
show database;
show tables;
show processlist;
show variables;
show status;

锁

锁的机制

共享锁读表操作加的锁，加锁后其他用户只能获取该表或行的共享锁，不能获取排它锁，也就是说只能读不能写
排它锁写表操作加的锁，加锁后其他用户不能获取该表或行的任何锁

锁的范围

行锁对某行记录加上锁
表锁对整个表加上锁

事务隔离级别

隔离级别	脏读	不可重复读	幻读	加锁读
READ-UNCOMMITTED	是	是	是	否
READ-COMMITTED	否	是	是	否
REPEATABLE-READ	否	否	是	否
SERIERLIZED	否	否	否	是

READ-UNCOMMITTED 读取未提交内容

当两个事务同时进行时，即使事务没有提交，所做的修改也会影响另一个事务内的查询

第一步：A 修改隔离级别，并开启事务，然后做一次查询操作

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@TX_ISOLATION;
+------------------+
| @@TX_ISOLATION   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | 333      |
+----+---------+----------+
3 rows in set (0.00 sec)

第二步：B 开启事务，然后做一次更新操作

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set password = 'ccc' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | ccc      |
+----+---------+----------+
3 rows in set (0.00 sec)

第三步：B 事务未提交，A 在事务内做一次查询操作，查询结果已经改变

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | ccc      |
+----+---------+----------+
3 rows in set (0.00 sec)

第四步：B 事务回滚

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | 333      |
+----+---------+----------+
3 rows in set (0.00 sec)

第五步：A 执行查询操作，查询结果变回去

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | 333      |
+----+---------+----------+
3 rows in set (0.00 sec)

READ-COMMITTED 读取提交内容

当两个事务同时进行时，只有在事务提交后，所做的修改才会影响另一个事务内的查询

第一步：A 修改隔离级别，并开启事务，然后做一次查询操作

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@TX_ISOLATION;
+----------------+
| @@TX_ISOLATION |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | 333      |
+----+---------+----------+
3 rows in set (0.01 sec)

第二步：B 开启事务，然后做一次更新操作

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set password = 'ccc' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | ccc      |
+----+---------+----------+
3 rows in set (0.00 sec)

第三步：B 事务未提交，A 在事务内做一次查询操作，查询结果没有改变

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | 333      |
+----+---------+----------+
3 rows in set (0.00 sec)

第四步：B 提交事务

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | ccc      |
+----+---------+----------+
3 rows in set (0.01 sec)

第五步：A 执行查询操作，查询结果发生变化

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | ccc      |
+----+---------+----------+
3 rows in set (0.00 sec)

REPEATABLE-READ 可重读

当两个事务同时进行时，即使事务提交了提交，所做的修改也不会影响另一个事务内的查询

第一步：A 修改隔离级别，并开启事务，然后做一次查询操作

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@TX_ISOLATION;
+-----------------+
| @@TX_ISOLATION  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | 333      |
+----+---------+----------+
3 rows in set (0.00 sec)

第二步：B 开启事务，然后做一次更新操作

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set password = 'ccc' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | ccc      |
+----+---------+----------+
3 rows in set (0.00 sec)

第三步：B 事务未提交，A 在事务内做一次查询操作，查询结果没有改变

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | 333      |
+----+---------+----------+
3 rows in set (0.00 sec)

第四步：B 提交事务

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | ccc      |
+----+---------+----------+
3 rows in set (0.00 sec)

第五步：A 执行查询操作，查询结果还是没有改变

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | 333      |
+----+---------+----------+
3 rows in set (0.00 sec)

第六步：A 提交事务，然后执行查询操作，查询结果发生变化

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | ccc      |
+----+---------+----------+
3 rows in set (0.00 sec)

SERIERLIZED 可串行化

当两个事务同时进行时，即使事务只是进行查询操作，也会对表或行加共享锁，另一个事务只能进行读操作

第一步：A 修改隔离级别，并开启事务，然后做一次查询操作

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@TX_ISOLATION;
+----------------+
| @@TX_ISOLATION |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+---------+----------+
| id | account | password |
+----+---------+----------+
|  1 | jack    | 111      |
|  2 | lily    | 222      |
|  3 | lucy    | 333      |
+----+---------+----------+
3 rows in set (0.00 sec)

第二步：B 开启事务，然后做一次更新操作，更新失败

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set password = 'ccc' where id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

主从备份

主数据库 192.168.1.100

修改数据库配置，并重启

server-id = 1
log_bin  = /var/mysqllog/mysql-bin.log
binlog_do_db = my_uni

添加用户

grant replication slave on *.* to 'myslave'@'%' identified by '123456';
show master status;

从数据库 192.168.1.101

修改数据库配置，并重启

server-id = 2
log_bin = /var/mysqllog/mysql-bin.log
replicate_do_db = my_uni

添加用户

change master to master_host='192.168.1.100', master_user='myslave', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=107;
show slave status;

启动，如果io/sql输出成功，则启动成功

start slave;
stop slave;
restart slave;

常用配置

# 允许所有网段访问
bind-address = 0.0.0.0

# 编码格式
character-set-server = utf8

# 最大连接数
max_connections = 1000

# 读取超时时间
net_read_timeout = 3000

# 连接超时时间
connect_timeout = 3000

# 最大传输大小
max_allowed_packet = 4194304000

# InnoDB表的索引、数据、插入数据的缓存大小
innodb_buffer_pool_size = 1024M

# 自增偏移数，主主备份常用
auto_increment_offset = 1

# 自增数，主主备份常用
auto_increment_increment = 10

PS：本文使用的是mysql-5.6

第六弹：MySQL常见问题处理
第六弹：MySQL常见问题处理力扣--mysql
MySQL面试指南
MySQL面试指南版本类常见问题：索引
centOs7中 mysql8.0 安装，配置，主从复制
mysql安装 mysql配置常见问题服务器安装mysql后远程无法连接，并报10061错误问题原因：服务器没...
SQL优化学习
一、学习框架 mysql常见问题[https://maimai.feishu.cn/mindnotes/bmncn...
MySQL主从库配置以及常见问题
此文档包含两部分：一、MySQL主从库的配置二、常见问题以及解决方法一、MySQL主从库的配置说明： 1...
软件技术--Mysql系列
1 安装mysql：常见问题： 1 Incorrect string value: '\xF0\x9F\x98\...
尚硅谷大数据技术之Oozie
第5章常见问题总结1）Mysql权限配置授权所有主机可以使用root用户操作所有数据库和数据表mysql> gr...
mysql常见问题
设置时间字段默认值
mysql常见问题
1.创建数据库mysql 2.迁移mysql数据目录(因mysql数据所在磁盘满了) 3.基本操作 4.主从同步 ...
mysql 常见问题
1、linux mysql5.7报err 1055错误 sql_mode=only_full_group_by 去...