mysql基本操作

Yum 安装

1
2
rpm -Uvh  http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
yum install mysql-server

主从同步/读写分离

master

1
2
3
4
5
6
7
8
server-id = 1  #(标识为master库)
log-bin=mysql-bin
binlog_format=mixed
binlog-ignore-db=mysql
replicate-ignore-db=mysql
binlog_cache_size = 1M
binlog_format=mixed
expire_logs_days=3
1
2
3
4
5
6
7
#1.创建账号并授权
GRANT REPLICATION SLAVE ON *.* TO backup@'%' IDENTIFIED BY 'backup';
flush privileges;
#2.锁表
FLUSH TABLES WITH READ LOCK;
#3.查看当前状态
show master status;


备份导出,并将SQL文件传输给slave服务器执行导入。

1
mysqldump -uroot  --all-databases > /tmp/mydb.sql

slave

1、修改配置文件

1
2
3
4
5
6
7
server_id=2
binlog-ignore-db=mysql #不记录binlog
replicate-ignore-db=mysql #不复制test库的binlog
log-bin=mysql-bin
binlog_cache_size = 1M
binlog_format=mixed
expire_logs_days=3

2、导入SQL文件

1
source mydb.sql;

3、开启同步

1
CHANGE MASTER TO MASTER_HOST='192.168.1.228',MASTER_USER='backup',MASTER_PASSWORD='backup',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=399;

4、设置只读
对于需要保证master-slave主从同步的salve库,如果要设置为只读状态,需要执行的命令为:

1
2
3
mysql> show global variables like "%read_only%";
#查看只读状态
mysql> set global read_only=1;

将salve库从只读状态变为读写状态,需要执行的命令是:

1
mysql> set global read_only=0;

  • 1.read_only=1只读模式,不会影响slave同步复制的功能,所以在MySQL slave库中设定了read_only=1后,通过 show slave status\G 命令查看salve状态,可以看到salve仍然会读取master上的日志,并且在slave库中应用日志,保证主从数据库同步一致;
  • 2.read_only=1只读模式,可以限定普通用户进行数据修改的操作,但不会限定具有super权限的用户的数据修改操作;在MySQL中设置read_only=1后,普通的应用用户进行insert、update、delete等会产生数据变化的DML操作时,都会报出数据库处于只读模式不能发生数据变化的错误,但具有super权限的用户,例如在本地或远程通过root用户登录到数据库,还是可以进行数据变化的DML操作;

5、检查状态

1
show slave status\G

检查是否和Master状态一致,并且两个线程是否为YES。

测试

在Master上执行以下语句,解除锁表状态。

1
UNLOCK TABLES

访问

查询

1
2
3
4
查询最新10条数据  顺序、倒序

select * from t_web_001_member LIMIT 10
select * from t_web_001_member ORDER BY memberId desc limit 10

更新

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

1、update
update table name set guarantee_id ='34' and guarantee_name = 'xxxx’ where demand_id = 1567;
2、insert into
insert into table(field1,field2) values(value1,value2)
3、delete
delete from table where 范围

-- select * from t_web_001_member where DATE(regtime) = '2017-09-13' and memberId > 165949;
select * from t_web_001_member where DATE(regtime) = '2017-09-12';
-- select * from t_web_001_member where DATE(regtime) = '2017-09-13' and memberId > 165949;


-- UPDATE t_web_001_member set regtime= DATE_FORMAT(ADDDATE(date_sub('2017-09-12 07:00:00',interval 0 day) , INTERVAL FLOOR(0 + RAND() * 120) MINUTE),'%Y-%m-%d %H:%i:%s') where DATE(regtime) = '2017-09-13' and memberId > 165949


insert into
INSERT INTO t_web_001_member(userName, mobile, loginpwd,regtime,mobile_state, isAccountIn) SELECT username, mobile, PASSWORD, create_time,1, 1 FROM t_user;

清空某列的数据
update t_web_001_member set regtime =null;

权限

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
5.7设置root密码
update mysql.user set authentication_string=password('123qwe') where user='root' and Host = 'localhost';

DATE_FORMAT(ADDDATE(date_sub('2017-09-05 07:00:00',interval -1 day) , INTERVAL FLOOR(0 + RAND() * 120) MINUTE),'%Y-%m-%d %H:%i:%s')
日期随机

mysqladmin -u root password "newpass"
mysqladmin -u root password “ios"


create database zabbix charset utf8;
create database car charset utf8;
grant all privileges on car.* to 'car'@'%' identified by 'xxxxxx';
flush privileges;

#给普通用户建库的权限
grant select,delete,update,create,drop,alter,insert on *.* to 'xx'@'%' ;

导出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
导入导出
-d 表结构
#导出数据和表结构
mysqldump -uroot -p dbname > dbname.sql
#导出多张表
mysqldump -uroot -password dbname table1 table2 >db.sql

#导出表结
mysqldump -uroot -p -d dbname > dbname.sql
#导出某张表的数据和表结构
mysqldump -uroot -pdbpasswd dbname >db.sql;
#导出某张表的表结构
mysqldump -uroot -pdbpasswd dbname test>db.sql;

导出部分字段不为空的数据
#select COUNT(*) from t_user where mobile !='' and real_name !='' and real_name !='Null'

binlog 查看

参数说明:
-v, –verbose 用于输出基于row模式的binlog日志,
-vv 为列数据类型添加注释 –base64-output=decode-rows 解码binlog里经过base64编码的内容

1
mysqlbinlog -vv --base64-output=decode-rows  mysql-binlog.xxxx > new.sql
------ 本文结束 ------

版权声明

Medivh's Notes by Medivh is licensed under a Creative Commons BY-NC-ND 4.0 International License.
Medivh创作并维护的Medivh's Notes博客采用创作共用保留署名-非商业-禁止演绎4.0国际许可证
本文首发于Medivh 博客( http://www.mknight.cn ),版权所有,侵权必究。