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