Category Archives: Mysql

Adding mysql columns to a large table

Changing a large Mysql table has become much easier. I just wanted to document using the pt-online-schema-change command. This following example adds a new column to a Users table.

# pt-online-schema-change  h=api.rds.amazonaws.com,t=Users,u=xxx
 --database xxx  --ask-pass --alter "ADD COLUMN (last_ip varchar(40))"   
--nocheck-replication-filters --critical-load Threads_running=100  --execute

Enter MySQL password:
Found 7 slaves:
  ip-10-xxx
  ip-10-xxx
  ip-10-xxx
  ip-10-xxx
  ip-10-xxx
  ip-10-xxx
  ip-10-xxx
Will check slave lag on:
  ip-10-xxx
  ip-10-xxx	
	....

# 8 software updates are available:
#   * The current version for MySQL Community Server (GPL) is 5.6.24.
#   * The current version for MySQL Community Server (GPL) is 5.6.24.
#   * The current version for MySQL Community Server (GPL) is 5.6.24.
#   * The current version for MySQL Community Server (GPL) is 5.6.24.
#   * The current version for MySQL Community Server (GPL) is 5.6.24.
#   * The current version for MySQL Community Server (GPL) is 5.6.24.
#   * The current version for MySQL Community Server (GPL) is 5.6.24.
#   * The current version for MySQL Community Server (GPL) is 5.6.24.

Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `xxx`.`Users`...
Creating new table...
Created new table xxx._Users_new OK.
Waiting forever for new table `xxx`.`_Users_new` to replicate to ip-xxxx...
Altering new table...
Altered `xxx`.`_Users_new` OK.
2016-03-30T13:55:11 Creating triggers...
2016-03-30T13:55:11 Created triggers OK.
2016-03-30T13:55:11 Copying approximately 3827686 rows...
Copying `xxx`.`Users`:  10% 04:18 remain

How to start a maria mysql server on amazon linux

It should be an easy task to restart a mysql server. However, I recently ran into this:

[root@ip-172-31-44-221 ~]# service mysqld start
Redirecting to /bin/systemctl start  mysqld.service
Failed to issue method call: Unit mysqld.service failed to load: No such file or directory.
[root@ip-172-31-44-221 ~]#

There was nothing in /etc/init.d setup for mysql. After an hour of research I discovered this:

 1006  systemctl start mariadb.service
 1007  systemctl enable mariadb.service
 1008  /usr/bin/mysql_secure_installation

Apparently after installation of maria db you need to use systemctl.