mysql


change root password

mysqladmin -u root password #return 即可
mysqladmin -uroot -p password ''

mysqldump

mysqldump -uroot -p database > /path/file.sql
mysql -uroot -p database < /path/file.sql
mysqldump --log-error=/root/error.log --default-character-set=utf8 --opt --flush-privileges --triggers -R --hex-blob --set-gtid-purged=OFF --single-transaction -uproject -pe

dw -h.mysql.rds.aliyuncs.com project > /root/.date +%Y-%m-%d.sql

主从复制

1.master端

1) my.cnf 
 log-bin=mysql-bin
 server-id=1   ##restart
 2) CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
 3) show master status;

2.slave端

1) server_id=2   ##restart 
 2) MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.1.121',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='slavepass',
    -> MASTER_LOG_FILE='master1-bin.000001',
    -> MASTER_LOG_POS=245;
 3) start slave
  1. 读写分离

  2. 性能优化

grant

grant select on mysql.* to lang@'%' identified by '123456';

mysqlgeneral日志和慢查询日志设置

show global variables like '%log%';
SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mariadb/general.log';
SET GLOBAL slow_query_log_file = '/var/log/mariadb/slow.log';

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

slow_query_log=1
slow_query_log_file=/var/log/mariadb/slow.log
log_output=file
long_query_time=1s

general-log=1
general-log-file=/var/log/mariadb/general.log

忘记密码

mysqld_safe --skip-grant-tables &
mysql -uroot
use mysql;
update user set password=PASSWORD("") where User='root';
flush privileges;

mysqlreport --user=root --password ''

show engine innodb status

delete from blog.insert_test where a='1';

恢复relay-bin文件里的所有数据 mysqlbinlog localhost-relay-bin.000002 | mysql -u root -p -f(-f忽略报错)

gtid_mode=ON
enforce-gtid-consistency=true

mysql> CHANGE MASTER TO
     >     MASTER_HOST = host,
     >     MASTER_PORT = port,
     >     MASTER_USER = user,
     >     MASTER_PASSWORD = password,
     >     MASTER_AUTO_POSITION = 1;

增加字段

alter table wp_users add passtest int(4) default '0';

增加索引

alter table wp_users add index index_test1(display_name);