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条记录