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
18
19
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