Database

导出 pg_dump -h IPADRESS -U USERNAME -t TABLENAME --column-inserts DATABASENAME > BACKUPNAME.sql 表owner批量授权 REASSIGN OWNEDBY old_role [,...] TO new_role 主从配置 主节点配置 # 安装postgre # 切换postgres su - postgres # 登录 psql # 管理员用户配置密码 ALTER USER postgres WITH PASSWORD 'YourPassWord'; # 创建备份账号及权限 CREATE ROLE replica login replication encrypted password 'replica'; # 验证账号是否成功 SELECT usename from pg_user; # 验证权限 SELECT rolname from pg_roles; # 编辑pg_hba.conf,设置replica用户白名单 vim /var/lib/pgsql/9.6/data/pg_hba.conf host all all <从节点的VPC IPv4网段> md5 #允许VPC网段中md5密码认证连接 host replication replica <从节点的VPC IPv4网段> md5 #允许用户从replication数据库进行数据同步 # 编辑postgresql.conf vim /var/lib/pgsql/9.6/data/postgresql.conf # 分别找到以下参数 listen_addresses = '*' #监听的IP地址 wal_level = hot_standby #启用热备模式 synchronous_commit = on #开启同步复制 max_wal_senders = 32 #同步最大的进程数量 wal_sender_timeout = 60s #流复制主机发送数据的超时时间 max_connections = 100 #最大连接数，从库的max_connections必须要大于主库的 # 重启服务 systemctl restart postgresql-9.6.service 从节点配置 # 运行以下命令使用pg_basebackup基础备份工具指定备份目录。 pg_basebackup -D /var/lib/pgsql/9.6/data -h <主节点IP> -p 5432 -U replica -X stream -P # 依次运行以下命令新建并修改recovery.conf配置文件。 cp /usr/pgsql-9.6/share/recovery.conf.sample /var/lib/pgsql/9.6/data/recovery.conf vim /var/lib/pgsql/9.6/data/recovery.conf # 分别找到以下参数，并将参数修改为以下内容： standby_mode = on #声明此节点为从库 primary_conninfo = ‘host=<主节点IP> port=5432 user=replica password=replica’ #对应主库的连接信息 recovery_target_timeline = ‘latest’ #流复制同步到最新的数据 # 运行以下命令打开postgresql.conf文件。 vim /var/lib/pgsql/9.6/data/postgresql.conf max_connections = 1000 # 最大连接数，从节点需设置比主节点大 hot_standby = on # 开启热备 max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间 wal_receiver_status_interval = 1s # 从节点向主节点报告自身状态的最长间隔时间 hot_standby_feedback = on # 如果有错误的数据复制向主进行反馈 # 运行以下命令修改数据目录的属组和属主 chown -R postgres.postgres /var/lib/pgsql/9.6/data 检测验证 # 检测验证需要主从节点之间存在数据交互，例如，从节点备份目录时，进行检测能够得到预期的结果。 pg_basebackup -D /var/lib/pgsql/96/data -h <主节点IP> -p 5432 -U replica -X stream -P # 在主节点中运行以下命令查看sender进程。 ps aux |grep sender # 在从节点中运行以下命令查看receiver进程。 ps aux |grep receiver # 在主节点中进入PostgreSQL交互终端，输入以下SQL语句，在主库中查看从库状态。 select * from pg_stat_replication;

常用命令 mysql导出 –skip-extended-insert 跳过多行写入 –skip-quote-names 跳过 ` 表名 –complete-insert 带字段的insert 结构导出 mysqldump -uroot -pxxx \ --default-character-set=utf8 \ --set-gtid-purged=off \ --compact \ --no-data \ --databases xxx \ --tables xxx > $(date +%Y%m%d%H%M%S)_struct.sql 数据导出 mysqldump -uroot -pxxx \ --default-character-set=utf8 \ --set-gtid-purged=off \ --compact \ --no-create-info \ --skip-quote-names \ --complete-insert \ --databases xxx \ --tables xxx > $(date +%Y%m%d%H%M%S)_data.sql 数据加条件导出 mysqldump -uroot -pxxx \ --default-character-set=utf8 \ --set-gtid-purged=off \ --compact \ --no-create-info \ --skip-quote-names \ --complete-insert \ --databases xxx \ --tables xxx \ --where="sqlxxx"> $(date +%Y%m%d%H%M%S)_data.sql 整库备份 # 建表语句+数据 mysqldump -u USER -p -h HOST -B DATABASENAME --single-transaction --default-character-set=utf8 --set-gtid-purged=off > DATABASENAME_$(date +%Y%m%d%H%M%S).sql binlog日志查看 mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.021530 >021530.sql 慢进程查看 select id,user,host,db,command,time,state,info from information_schema.PROCESSLIST order by time desc; Slave 链接 master 配置 CHANGE MASTER TO MASTER_HOST='IPADDRESS',MASTER_USER='UserName',MASTER_PASSWORD='PassWord',master_log_file='BinLogFile',master_log_pos=POSITION; 从库扩容备份操作 1. 备份到目标机器: innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user=root --password="PassWord" --compress --parallel=4 --compress-threads=4 --stream=xbstream /tmp/backup | ssh root@IPADDRESS "xbstream -x -C /DSTDIR" 2. 从库扩容操作: 目标机器解压 innobackupex --parallel=8 --decompress ./ 目标机器初始化 innobackupex --use-memory=51200M --apply-log ./ 目标机器恢复 innobackupex --defaults-file=/etc/my.cnf --copy-back ./2017-08-23_21-23-46/ 清理备份文件 find /var/lib/mysql -name "*.qp" | xargs rm chown -R mysql.mysql /var/lib/mysql mysql 从库繁忙配置： innodb_flush_log_at_trx_commit = 2 sync_binlog=1 set global innodb_flush_log_at_trx_commit=0; set global sync_binlog=0; mysql8.0密码更改原生： set global validate_password.policy=0; set global validate_password.length=1; ALTER user 'root'@'localhost' IDENTIFIED BY 'PASSWORD'; ALTER USER 'UserName'@'%' IDENTIFIED WITH mysql_native_password BY 'PassWord'; mysql slave 权限配置： CREATE USER 'rpl'@'172.16.1.%' IDENTIFIED BY 'xxx'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rpl'@'172.16.1.%'; information_schema 表空间优化 碎片大小 = 数据总大小 - 实际表空间文件大小 数据总大小 = Data_length + Index_length = 101842944 实际表空间文件大小 = rows_Avg_row_length = 101177624 碎片大小 = (101842944 - 101177624) / 1024 /1024 = 0.63MB ...