welcome: please sign in
location: MySQL

Some useful notes about MySQL.

Backup & Restore

   1 mysqldump -u root -p --all-databases | gzip > db-backup.sql.gz
   2 mysql -u root -p < db-backup.sql

Binary Logs

Disable binary log if no replication nor data recovery needs.

Otherwise, to avoid bin logs from eating up all the disk, setup a expire time of it.

In mysql shell (as root):

   1 mysql> SET GLOBAL expire_logs_days = 15;

And in my.cnf add:

   1 [mysqld]
   2 # binary logs expired after 15 days
   3 expire-logs-days=15

Sets the expire time of bin logs to 15 days.

To delete exist bin logs, use either:

   1 PURGE BINARY LOGS TO 'binlogname';
   2 PURGE BINARY LOGS BEFORE 'datetimestamp';
   3 
   4 # e.g.
   5 PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 15 DAY) + INTERVAL 0 SECOND;

Replication

In mysql shell (as root):

   1 grant replication slave on *.* to slave_of_node1@'%' identified by 'password-for-node2';
   2 flush privileges;
   3 flush tables with read lock;
   4 show master status;
   5 # note down master status and dump the db
   6 unlock tables;
   7 # then on node2
   8 change master to master_host='ip.of.node.1',master_user='slave_of_node1',master_password='password-for-node2',master_log_file='mysql-bin.000002',master_log_pos=12345;
   9 start slave;
  10 show slave status\G

Change Password

Change password for normal user (login as root)^[2]:

   1 UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE USER='user-name-here' AND Host='host-name-here';

Ref.

[1].Is it safe to delete mysql-bin files? - StackExchange

[2].MySQL Change a User Password

MySQL (last edited 2017-04-03 03:01:54 by AstroProfundis)

How many stars in your bowl, How many sorrows in your soul?
CopyRight © 2011-2017 Allen Zhong, under a CC BY-NC-ND 4.0 License. | IPv6 Enabled.