In case you want to store special characters inside of MySQL table field, you need to change the table encoding like below:

ALTER TABLE YOUR_TABLE CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

#database #mysql #charset #character_set #collate #utf8 #utf8_unicode_ci #alter

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

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