美文网首页
MySQL 常见问题

MySQL 常见问题

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

官网: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 常见问题

      本文链接:https://www.haomeiwen.com/subject/rmrfittx.html