美文网首页
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

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|MySQL 常见问题|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！