Saturday, December 10, 2016

remove old mysql binary logs

The binary logs can use lots of storage space. Following are 2 ways to clean them up, by name and by time. 

To remove all binary logs up to mysql-bin.003456:

mysql> PURGE BINARY LOGS TO 'mysql-bin.003456';

To remove all binary logs by date/time (Before December 8th, 2016 @6:00):

mysql> PURGE BINARY LOGS BEFORE '2016-12-08 00:06:00';

Search for MySQL binary logs, error logs, temporary files:

# lsof -nc mysqld | grep -vE '(.so(..*)?$|.frm|.MY?|.ibd|ib_logfile|ibdata|TCP)'

Tuesday, December 6, 2016

Enable https for Nginx

Modify listen line in config file for example /etc/nginx/sites-enabled/default.

listen 80 default_server;

and add listen 443 and certificate/key path. Following example is for Let's Encrypt installation.

listen 80;
listen 443 default ssl;
ssl_certificate /etc/letsencrypt/live/domainname/cert.pem;
ssl_certificate_key /etc/letsencrypt/live/domanname/privkey.pem;

To force http to https, add following in the server block say just after above entries. Myserver.com is used as server_name in following example.

server_name myserver.com;
if ($scheme = http) {
      return 301 https://$server_name$request_uri;
}

Check syntax error in config file:

nginx -t

Reload or restart Nginx server.

/etc/init.d/nginx reload|restart

Saturday, December 3, 2016

Reset MySQL root password


Following is a way to reset MySQL root password when you forgot.
  1. Stop MySQL: /etc/init.d/mysqld stop
  2. Startup MySQL without password prompt: mysqld_safe --skip-grant-tables &
  3. Login to MySQL: mysql --user=root mysql
  4. Reset password: mysql> update user set Password=PASSWORD('new-password') where user='root';
  5. mysql> flush privileges;
  6. mysql> exit;
  7. Kill running mysql daemon: kill mysql-pid
  8. Start MySQL: /etc/init.d/mysqld start