mysql数据库使用文档

1、mysql数据库导出导入

mysql im_db -uroot -p123456 -e "select * from t_im_group_message where create_time between  '2019-02-15 00:00:00'  and '2019-03-02 00:00:00';" > dbbak20190301  导出数据库某张表某时间段的数据（库：im_db  表t_im_group_message）

mysqldump --skip-lock-tables --single-transaction -uroot -p123456 im_db t_im_group_message > dump20190301.sql   导出数据库表的数据（库：im_db  表t_im_group_message）

select * from t_im_group_message where create_time between  '2019-02-15 00:00:00'  and '2019-03-02 00:00:00';  查询数据库某张表某时间段的数据（表：t_im_group_message 字段：create_time）

mysqldump -uroot -p123456 lxtx > lxtx.sql   数据导出
mysql -uroot -p123456 lxtx < lxtx.sql       数据导入

mysqldump --skip-lock-tables --single-transaction --flush-logs --master-data=2  -uroot -p123456 --databases im_config im_db lxtx_im_admin lxtx_wallet lxtx_wallet_admin > dbbackup20190216.sql（在线做主从使用）

mysql -uroot -p123456 -h 10.3.101.120
source /home/mysql_bak/dbbackup20190216.sql;

2、mysql数据库授权
SET PASSWORD FOR 'root'@'localhost' = password("123456");  设置root密码
授权：
grant all privileges on *.* to  'root'@'localhost' IDENTIFIED BY '123456';
grant all privileges on *.* to  'root'@'127.0.0.1' IDENTIFIED BY '123456';
grant all privileges on im_db.* to  'lxtx_pg'@'localhost' IDENTIFIED BY '123456';
grant all privileges on lxtx_im_admin.* to  'lxtx_pg'@'localhost' IDENTIFIED BY '123456';
grant all privileges on lxtx_wallet.* to  'lxtx_pg'@'localhost' IDENTIFIED BY '123456';
grant all privileges on lxtx_wallet_admin.* to  'lxtx_pg'@'localhost' IDENTIFIED BY '123456';
grant all privileges on *.* to  'root'@'localhost' IDENTIFIED BY '123456';
grant all privileges on *.* to  'root'@'127.0.0.1' IDENTIFIED BY '123456';
grant all on *.* to 'programuse'@'192.168.11.%' identified by '123456';
flush privileges;

select user,host,password from mysql.user;   查看用户权限

Delete FROM user Where User='lxtx_pg' and Host='localhost';  删除用户

drop user 'repl'@'10.3.101.120';
drop user 'repl'@'148.66.11.199';

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY '123456';
mysql数据库配置权限：
grant all privileges on *.* to  'root'@'localhost' IDENTIFIED BY '123456';
grant all privileges on *.* to  'root'@'127.0.0.1' IDENTIFIED BY '123456';
grant all privileges on app_manage.* to  'lxtx_pg'@'localhost' IDENTIFIED BY '123456';
grant all privileges on cailu.* to  'lxtx_pg'@'localhost' IDENTIFIED BY '123456';
grant all privileges on gtdollar.* to  'lxtx_pg'@'localhost' IDENTIFIED BY '123456';
grant all privileges on sns_pm.* to  'lxtx_pg'@'localhost' IDENTIFIED BY '123456';
grant all privileges on app_management.* to  'lxtx_pg'@'localhost' IDENTIFIED BY '123456';
flush privileges;

3、主从同步
use mysql;
select host, user from user;
grant all on *.* to 'programuse'@'172.18.133.%' identified by '123456';               主从
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'172.18.133.113' IDENTIFIED BY '123456';  master配置
mysql -uprogramuse -p123456 -h 172.18.133.113  测试
从同步到主：
stop slave;
show master status;   #master运行,查看master_log_file、master_log_pos信息

change master to
master_host='172.18.133.112',
master_user='replication',
master_password='123456',
master_log_file='mysql-bin.000004',
master_log_pos= 107;

start slave;
show slave status\G;

4、binlog日志查询

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000133 

5、数据库表查询

select * from  t_im_message order by create_time DESC limit 10;   查询表最后10条记录