https://dba.stackexchange.com/questions/41050/is-it-safe-to-delete-mysql-bin-files

#mysql #mysql_bin #bin #bin_file #purge

In order to run a mySQL query from command line you can use -e parameter:

mysql -u <user> -p -e "select * from schema.table"

#mysql #command #query

There is always a risk and also a problem when altering a production mySQL table. Percona has released a toolkit that contains a command called pt-online-schema-change. It will change table schema live on production without downtime.

Installation steps on Debian:

1- wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb

2- sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb

3- sudo apt-get update

4- sudo apt-get install percona-toolkit

Now you have percona toolkit on your Debian server. Use the command pt-online-schema-change for your table alteration.

#mysql #percona #schema #alter_table #online_schema_change #percona_toolkit #pt_online_schema_change

In order to dry run before the real execution use --dry-run:

pt-online-schema-change --dry-run  h=127.0.0.1,D=YOUR_DB,t=YOUR_TABLE --alter "ADD COLUMN (foobar varchar(30) DEFAULT NULL);"

Now after dry run you can execute the alter command:

pt-online-schema-change --execute  h=127.0.0.1,D=YOUR_DB,t=YOUR_TABLE --alter "ADD COLUMN (foobar varchar(30) DEFAULT NULL);"

#mysql #percona #schema #alter_table #online_schema_change #percona_toolkit #pt_online_schema_change

https://dba.stackexchange.com/questions/187630/problem-with-aborted-pt-online-schema-change-command

#mysql #trigger #percona #online_schema_change

With mysqldump you can export databases. with --port parameter you can specify which port it should connects. If you provide localhost for --host parameter, mySQL will use sockets and port will be ignored.

So be careful with it!

#mysql #mysqldump #port #port_ignorance #3306 #backup #database_backup #sockets #ip_address #localhost

Table compression in MySQL:

https://dev.mysql.com/doc/refman/5.5/en/innodb-compression-usage.html

#database #mysql #compression #innodb

If you have space problems on a server that hosts MySQL database, it's good idea to use compression. Make sure you are using InnoDB storage engine.

In order to compress data on a table:

alter table YOUR_DB_NAME.YOUR_TABLE_NAME ROW_FORMAT=COMPRESSED;

The output in my case squeezed data 4X:

ls -lh users.ibd | awk '{print $5}'
 16G

After compression:

ls -lh users.ibd | awk '{print $5}'
 3.9G

NOTE: you have to use innodb_file_per_table in your configuration. We have previously talked about this procedure step by step.

#mysql #innodb #compression #alter #row_format #compressed

How to clone a database in MySQL?

mysqldump -u root db_name | mysql -u root new_db_name

NOTE: if it gets password provide -p to both commands.

#mysql #clone #copy #database #copy_database

Backup mysql database and gzip 9 it:

mysqldump -u $user -h $host --port=$port --password=$password $db_name | gzip -9 > "$backup_file_name"

#mysql #backup #gzip9