Vagrant bug (Warning: Authentication failure. Retrying...)

I was setting up several MySQL databases using Vagrant for testing and I kept getting this Warning which was preventing me from using the VMs:

Warning: Authentication failure. Retrying...

After wasting lots of time and reading up on people's post I was able to get it working. 

Patch info is here:

https://github.com/mitchellh/vagrant/issues/7610

Work around steps:

go to this dir:

/opt/vagrant/embedded/gems/gems/vagrant-1.8.5/plugins/guests/linux/cap

vi public_key.rb (need to run as sudo)

Look for this code:

            if test -f ~/.ssh/authorized_keys; then

              grep -v -x -f '#{remote_path}' ~/.ssh/authorized_keys > ~/.ssh/authorized_keys.tmp

              mv ~/.ssh/authorized_keys.tmp ~/.ssh/authorized_keys

Add this below that code:

chmod 0600 ~/.ssh/authorized_keys

End result will look like this:

Run "vagrant up" again

Setting up master-master or master-slave replication (without SSL)

This post has directions for setting up Master-Master replication and Master-Slave replication for testing purposes. The first part is for Master-Master replication. The second part is for Master-Slave. The directions are similar for master-master and master-slave but slightly different. These directions do not use SSL. Without SSL it is possible for hackers to sniff passwords and data transferred.

Part 1: Master-Master Replication

Setting up master-master replication is like setting up each master to be a slave of the other. These directions detail the steps on how to set up master-master replication between two nodes.

Step 1: Setup config file for each master master

Edit config files for first master server (/etc/mysql/my.cnf) to include the following settings. Some of the values may vary on your server, such as the log-bin location, the binlog format (STATEMENT/MIXED instead of ROW). You may already have some of these values in your my.cnf, especially the server-id)

Note that server-id needs to be unique. auto_increment_increment and auto_increment_offset are needed to avoid primary key conflicts on auto increment columns. One of the master will have auto_increment_increment = 2, auto_increment_offset = 1 while the second master will have auto_increment_increment = 2, auto_increment_offset = 2.

First Master:
[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/log-bin.index
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days    = 2
log_slave_updates   = 1
auto_increment_increment = 2
auto_increment_offset = 1
max_binlog_size     = 100M
binlog_format=row

Second Master:
[mysqld]
server-id=2
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/log-bin.index
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days    = 2
log_slave_updates   = 1
auto_increment_increment = 2
auto_increment_offset = 2
max_binlog_size     = 100M
binlog_format=row

It may be necessary to restart the MySQL service on each master if this is the first time setting up replication on this master.

/etc/init.d/mysql restart

If MySQL fails to start, check the MySQL error log for details. Also, check to make sure that each of the log files, .info files and .index files in the above added parameters exists and have the correct file ownership of mysql.mysql.
You can fix the ownership like this:

chown -R mysql.mysql /var/log/mysql

Step 2: Check the status of the first master:

show master status;

Copy the values to a notepad window for possible use later.

Step 3: Create a replication user on both master servers

This is the user that each master-slave will use to connect to the other master.

CREATE USER 'replication'@'%' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

Step 4: Test that the replication user can connect to each server

Run this from the command line on each master to connect to the other master:
mysql -u replication -p -h YOURMASTERNODE

Step 5: Create a dumpfile from the master

If your server doesn't have any databases on it and is a fresh MySQL install then you can skip to Step 8.

Run mysqldump on one of the Master servers (note the option of --master-data in the mysqldump command, this is needed to give you the point in time from which to start replication, this will do some locking so be careful when running it).
Also note that choosing --all-databases will dump all databases including the mysql schema, performance schema and information schema. The MySQL schema has the privileges and users which if restored on your slave it will over-write any existing users. To preserve your user permissions, it would be better to dump only the specific databases you want so as to not include the MySQL, performance_schema and information_schema using the --databases option.
mysqldump -u username -p --master-data --all-databases > dump.sql
OR
# Copy this into a bash script file on your server and execute it:
#################################################################################
MYSQL_USER=root
MYSQL_PASS=PASSWORD
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"

DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}

DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done

MYSQLDUMP_OPTIONS="--master-data --routines --triggers --events --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > dump.sql
#################################################################################

Step 6: Restore dumpfile from the master on the slave

mysql -u username -p < dump.sql
If you are only importing one database you would run the restore like this:
mysql -u username -p DATABASENAME < dump.sql

Step 7: Give yourself a motivational pep talk

You are awesome. You can do this. Soon you will have two minions to do all your database replicating for you. Pat yourself on the back for making it this far and continue onto the next step.

Step 8: Configure Slave Process

Look at the first 25 lines or so from your dump file or if you skipped steps 5-7, use results from "SHOW MASTER STATUS" (Step 2 earlier)
Here is how you would get the first 25 lines from your dump file:

head -25 dump.sql
It should have something like this:
--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Copy those values.

Log onto your second master server and use the above copied values to configure it as a slave of the first master server. This is the command you will run but replacing the appropriate values for MASTER_HOST, MASTER_USER and MASTER_PASSWORD (change the MASTER_LOG_FILE and MASTER_LOG_POS to match up with the values extracted from the dump file or from Step 2):

CHANGE MASTER TO MASTER_HOST='YOURMASTERNODE2',MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;

Step 9: Start Slave Process on first master

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

If you get errors, reset the slave and redo step 8:

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)

CHANGE MASTER TO MASTER_HOST='YOURMASTERNODE',MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

Step 10: Slave Status

mysql> show slave status

Make sure to check the below parameter status should be "YES" and the remaining values are appropriate.

 Slave_IO_Running: Yes
Slave_SQL_Running: Yes
If "Slave_SQL_Running" shows "No", then look in the "Last SQL Error" column. There might be one query causing problems with replication and you can tell MySQL to ignore it and keep going:
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
mysql> show slave status;

If you are setting up replication on a VM that was copied from another (vmware, vagrant, virtual box) system you may get this error:
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

In order to resolve you will need to remove the file at /var/lib/mysql/auto.cnf on one or more of your servers and restart MySQL. MySQL will automatically regenerate that file with a unique UUID when it restarts.

Stop and Start the slave again.

Step 11: Test the replication

Now you have setup one of the masters to be a slave of the other master. Create a table with an auto incrementing column and insert some rows on the master server and see if they are replayed on the slave. Select statements are not replayed on the slave so don't worry about those.

CREATE DATABASE IF NOT EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test`. `test_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `attribute` varchar(45) NOT NULL COMMENT 'The attribute',
  `value` varchar(255) DEFAULT NULL COMMENT 'The value of the attribute',
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `IDX_attribute` (`attribute`)
) ENGINE=InnoDB CHARACTER SET='utf8'  COLLATE='utf8_unicode_ci' COMMENT='This is a test table for testing replication';

SET NAMES utf8;

INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'host name ',  @@hostname, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'server id ', @@server_id, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'version ', @@version, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'TEST DATA', 'English: test - Japanese: 試し - Chinese: 试验 - Arabic: اختبار', now());

SELECT * FROM `test`.`test_table`;

You should notice that the id column has all odd numbers because there were written while connected to the first master.

Step 12: Check the status of the second master

show master status;
Copy the values to a notepad window.

Step 13: Repeat slave process on second master

Repeat the steps from 8,9,10 on the second master until you get replication working from both servers.

Step 14: Test auto-increment offsets

Now you have setup master-master replication! Add some more rows to the table you created in Step 11 but from the other master and make sure the primary keys are correctly off-setted and do not conflict with existing rows.

SET NAMES utf8;

INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'host name ',  @@hostname, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'server id ', @@server_id, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'version ', @@version, now());

SELECT * FROM `test`.`test_table`;



You should notice that the values from the first master replicated over with the odd numbers and the values written while connected to the second master have all even numbers. This is because of the offset values provided in the my.cnf file.

Part 2: Master-Slave Replication

Setting up Master - Slave Replication

Step 1: Setup config file for master

Edit config files for master server (/etc/mysql/my.cnf). Some of the values may vary on your server, such as the log-bin location, the binlog format (STATEMENT/MIXED instead of ROW). If you are adding a slave off of an existing master-master then you will need the auto_increment_increment and auto_increment_offset to be included in your my.cnf file, however, if you are only creating a master-slave without master-master-slave then that will not be needed.

[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days    = 2
log_slave_updates   = 1
#auto_increment_increment = 2
#auto_increment_offset = 1
max_binlog_size     = 100M
binlog_format=row

It may be necessary to restart the MySQL service on the master if this is the first time setting up replication on this master.
/etc/init.d/mysql restart

Step 2: Check that status of the master

show master status;

Copy the values to a notepad window for possible use later.

Step 3: Create a replication user on the master server

This is the user the slave will use this to connect with:

CREATE USER 'replication'@'%' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

Step 4: Setup config file for slave(s)

Edit the my.cnf for the slave(s) (/etc/mysql/my.cnf). Some of the values may vary on your server, such as the log-bin location, the binlog format (STATEMENT/MIXED instead of ROW). Each slave server-id needs to be unique.

[mysqld]
server-id=3
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days    = 2
log_slave_updates   = 1
max_binlog_size     = 100M
binlog_format=row
read_only=1

Restart the MySQL service on the slave after making changes. Setting the slave to read_only is only effective against users that do not have SUPER access.

/etc/init.d/mysql restart

If MySQL fails to start, check the MySQL error log for details. Also, check to make sure that each of the log files, .info files and .index files in the above added parameters exists exist and have the correct file ownership of mysql.mysql.

Step 5: Create a dumpfile from the master

If your server doesn't have any databases on it and is a fresh MySQL install then you can skip to Step 8 and use the results from Step 2 for MASTER_LOG_FILE and MASTER_LOG_FILE.

Run mysqldump on the Master server (note the option of --master-data in the mysqldump command, this is needed to give you the point in time from which to start replication, this will do some locking so be careful when running it).

Also note that choosing --all-databases will dump all databases including the mysql schema. The MySQL schema has the privileges and users which if restored on your slave it will over-write any existing users. It may be better to dump only the specific databases you want so as to not include the MySQL, performance_schema and information_schema using the --databases option.

mysqldump -u username -p --master-data --all-databases > dump.sql

OR

# Copy this into a bash script file on your server and execute it:

#################################################################################
MYSQL_USER=root
MYSQL_PASS=PASSWORD
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"

DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}

DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done

MYSQLDUMP_OPTIONS="--master-data --routines --triggers --events --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > dump.sql
#################################################################################

Step 6: Restore dumpfile from the master on the slave

mysql -u username -p < dump.sql
If you are only importing one database you would run the restore like this:
mysql -u username -p DATABASENAME < dump.sql

Step 7: Connection Testing (Slave to Master)

Run this from the command line:
mysql -u replication -p -h YOURMASTERNODE

Step 8: Configure Slave Process

Look at the first 25 lines or so from your dump file or use results from "SHOW MASTER STATUS" (if you have a MySQL instance with no database).
head -25 dump.sql
It should have something like this:
--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Copy those values.
Log onto the slave server. Use the above copied values to configure the slave like this with the appropriate values for MASTER_HOST, MASTER_USER and MASTER_PASSWORD (change the MASTER_LOG_FILE and MASTER_LOG_POS to match up with the values extracted from the dump file):
CHANGE MASTER TO MASTER_HOST='YOURMASTERNODE',MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;

Step 9: Start Slave

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

If you get errors, reset the slave and redo step 8:

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)

CHANGE MASTER TO MASTER_HOST='YOURMASTERNODE',MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

Step 10: Slave Status

mysql> show slave status
Make sure to check the below parameter status should be "YES" and the remaining values are appropriate.
 Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If "Slave_SQL_Running" shows "No", then look in the "Last SQL Error" column. There might be one query causing problems with replication and you can tell MySQL to ignore it and keep going:

mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
mysql> show slave status;

If you are setting up replication on a VM that was copied from another (vmware, vagrant, virtual box) system you may get this error:
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

In order to resolve you will need to remove the file at /var/lib/mysql/auto.cnf on one or more of your servers and restart MySQL. MySQL will automatically regenerate that file with a unique UUID when it restarts.
Stop and Start the slave again.

Step 11: Test the replication

Create a table and insert some rows on the master server and see if they are replayed on the slave. Select statements are not replayed on the slave so don't worry about those.

-- SQL to run on the MASTER:

CREATE DATABASE IF NOT EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test`. `test_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `attribute` varchar(45) NOT NULL COMMENT 'The attribute',
  `value` varchar(255) DEFAULT NULL COMMENT 'The value of the attribute',
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `IDX_attribute` (`attribute`)
) ENGINE=InnoDB CHARACTER SET='utf8'  COLLATE='utf8_unicode_ci' COMMENT='This is a test table for testing replication';


SET NAMES utf8;

INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'host name ',  @@hostname, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'server id ', @@server_id, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'version ', @@version, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'TEST DATA', 'English: test - Japanese: 試し - Chinese: 试验 - Arabic: اختبار', now());

-- SQL to run on both the MASTER and SLAVE to verify replication worked:

SELECT * FROM `test`.`test_table`;

A couple interesting articles

Article 1:

WHY UBER ENGINEERING SWITCHED FROM POSTGRES TO MYSQL
https://eng.uber.com/mysql-migration/

Article 2:

It’s the data, stupid: Why database admins are more important than ever
Specialized databases, cloud, and DevOps expand, not eliminate, role of the DBA.

I thought this was a great article. See the link for the source. I've copied the text for posterity...

http://arstechnica.com/information-technology/2016/07/its-the-data-stupid-why-database-admins-are-more-important-than-ever/

SEAN GALLAGHER - 7/27/2016, 6:00 AM

It may not be all about the tables anymore, but the DBA role is still essential—even if the person doing it doesn't have the title.
Michael Mandiberg
The Rise of Specialized Databases

Terabyte terror: It takes special databases to lasso the Internet of Things
Attacking your monolithic database with a swarm—a whole swarm!
Power tools: Sorting through the crowded specialized database toolbox
To SQL or NoSQL? That’s the database question
View more stories
20
For those of us who have been in the information technology realm for too long, the title "database administrator" conjures up very specific images. We picture someone pulling hair out over issues with backups or snapshots not happening, schemas growing out of control, capacity plans blown up by new application demands, sluggish queries, and eternal performance tuning.

That old-school role of the DBA still exists in some places, particularly large enterprises where giant database clusters still rule the data center. But virtualization, cloud data storage, micro-services, the "DevOps" approach to building and running applications, and a number of other factors have significantly changed how organizations store and manage their data. Many of the traditional roles of the DBA seem to be moot in the shiny, happy world promised by the new generation of databases.

"NoSQL" databases don't require a pre-defined schema, and many have replication built in by default. Provisioning new servers can be reduced to clicking a few radio buttons and check boxes on a webpage. Development teams just point at a cloud data store such as Amazon Web Services' Simple Storage Service (S3) and roll. And even relational database vendors such as Oracle, Microsoft, and IBM are pushing customers toward data-as-a-service (DaaS) models that drastically simplify considerations about hardware and availability.

You might expect this to mean that DBAs' jobs are getting easier. If so, your expectations would be wrong.

"I think [DBAs'] roles have become much more complex," said Chris Lalonde, vice president and GM of Data at Rackspace. "While there is definitely more automation and tooling, the counter to that is that many of the newer technologies are less mature and require more care and feeding. I would say that many of the traditional tasks of DBAs still exist today or need to exist."

In fact, all these great new database technologies highlight the data professional, whether that person is called a DBA, data architect, data engineer, or, in some cases, data scientist. "Data is even more important today," said Kenny Gorman, a database veteran and co-founder of the real-time data service company Eventador. "Businesses used to rely on databases to be sound, run smoothly, and give good reporting. But now, data actually makes you more competitive, and there are more job titles working with data and more technologies that use it. And the database professional is at the core of that."

One step forward...

Non-relational platforms offered a promise to reduce the workload of DBAs, and in some ways they do. Ravi Mayuram, senior vice president of products and engineering at CouchBase, compared the shift in what DBAs have to do to how driving a car has changed over the years: once upon a time, "to drive one you had to essentially be an engineer, and when something went wrong you needed to pull to the side of the road and get under the hood." Now most things take care of themselves, he said, "but I can't do anything myself to fix it."

Databases such as MongoDB and CouchBase, while not relational, support SQL queries, and they have other aspects that make them approachable to experienced DBAs. But they also allow for "dynamic deployment decisions, which you couldn't do with relational systems," Mayuram claimed. "Adding new data structures used to require a schema change and downtime."

Data as a Service has been embraced for "a fraction of what companies do," Mayuram said. "Most companies don't have mission critical information in the cloud."
While a big relational database system requires an understanding of everything about the hardware and software stack, "the next generation of DBAs will be less involved in that," Mayuram explained. "There will be a requirement for a DBA—someone who is more database intensive, but not exclusively," to focus on tasks like capacity planning. The DBA of the future will need to know when to provision more servers and when to retire them.

That sort of dynamic scalability is what has driven the adoption of cloud-based data services based on specialized databases and “Data-as-a-Service” schemes (either built in-house or hosted with a vendor). In either case, provisioning services can take care of setting up hardware, network, and storage. In theory, the DBA focuses on figuring out when applications will need more database capacity. "This is a DevOps sort of role, dealing with dynamic provisioning—it's a slightly different profile," Mayuram said. "With more efficiency, they'll need a fraction less of DBA skills, but that requires them to be more capacity planners and understand the development side better."

For those unfamiliar with the term, DevOps is a practice now used widely in Web and service development. It describes application development teams working in collaboration with IT operations staff to continually improve performance, automation, and scalability of software and systems. The DevOps approach has been a major driver of the adoption of NoSQL databases and other non-traditional data storage and query technologies. DevOps has driven the development of Data-as-a-Service—largely because of the need to automate the scaling up and down of database capacity. But even in the purely relational world, the shift toward turning databases into a cloud service is reducing the need (and the ability) for DBAs to have fine-grain control over hardware configuration.

So far, Data as a Service has been embraced for "a fraction of what companies do," Mayuram said. "Most companies don't have mission critical information in the cloud." Early adopters, he noted, are taking a hybrid approach, with some creating internally hosted DaaS platforms based on cloud computing platforms within their own data centers. But other companies are largely keeping their critical relational systems as they are and using cloud approaches for new projects. "They still have DBAs taking care of existing apps and have DevOps teams handling database deployment in a micro services environment—services that don't need to be in a relational system," Mayuram explained.

With companies preserving their relational databases and increasingly needing to bridge the gap between the old and the new, things got more complex instead of simpler. And even when organizations completely outsource the applications that have typically placed the biggest demands on DBAs, they're still left with the need for some sort of data professional to make sense of what they've gotten themselves into.

The database schema for MediaWiki, the platform used by Wikipedia. Some specialized databases don't explicitly require schemas, but the schema lives on in other ways--and understanding data structures remains important.
Enlarge / The database schema for MediaWiki, the platform used by Wikipedia. Some specialized databases don't explicitly require schemas, but the schema lives on in other ways--and understanding data structures remains important.
Timo Tijhof
The news of death greatly exaggerated

In December 2013, Kenny Gorman wrote an article provocatively titled, "The Database Administrator is dead." But he concluded that article with the declaration, "Long live the DBA."

"The point of that piece was to say that the DBA is still important," Gorman told Ars recently. As a long-time Oracle database administrator and data architect at companies that included PayPal and eBay, Gorman found himself diving into MongoDB at Shutterfly and becoming a NoSQL believer. In the article, which he wrote while chief architect at the data-as-a-service provider ObjectRocket, he noted, "most of our customers don’t have DBAs on staff." But that didn't mean the job had gone away.

"As we move to the cloud," Gorman explained, "with data services, micro-services, the whole 'serverless' movement (services like Amazon Web Services' Lambda and Google Cloud Functions), the whole data landscape is continuing to evolve. And it has changed the role of the DBA—it's no longer the guy who manages the Oracle server in the data center for a particular company. Now there's database storage technologies that exist all over the cloud in various forms that they have to manage."

Even though many of these new database technologies automate much of what DBAs used to do, it doesn't mean that there's a reduction in DBAs' workload. "I believe automation has reduced the need for traditional Ops folks as they help scale the hardware and therefore the volume of queries," Lalonde said. "But there aren't many tools around finding and fixing slow queries or picking the best shard keys," he explained. "I believe automation allows for larger scale with fewer resources, but ultimately you still need an expert around."

Gorman believes the complexity of the new data environment is making DBAs' jobs even harder than before—not easier. That's in part because DBAs can't be as specialized as they once were. "I ran database servers for PayPal and eBay way back in the day," he explained, "and we had one or two data technologies, not fifty. If you knew Oracle, you could probably figure out Microsoft SQL Server—those technologies are complementary." That's not the case anymore, Gorman asserted. "These days, you have to understand the difference between Elasticsearch, Hadoop, [Apache] Kafka, and Oracle and how they are different and why is one better for a particular use case at hand than another."

Because of the pace of change in data storage and query technology, it's not even clear what a database is anymore. And many of the technologies being passed off to data professionals, regardless of their title, bear little resemblance to anything they've worked with before.

"Our careers evolved from managing systems and storage around databases," Gorman said. "Oracle was clearly a database. But these days, the very notion of what a database is has changed. Like, is Hadoop a database?" At ObjectRocket, Gorman built data services around MongoDB. "That's pretty clearly a database," he said, "but our new startup is based on [Apache] Kafka—is that a database?" (Kafka is a data broker that provides streams of data from query "subscriptions" for real-time applications.) "Well, it has properties of a database. It's meant to shuffle data, real-time data. So, it's this crazy evolution where we don't even know if a data product or data infrastructure is really a database anymore. The waters are so muddy. Now it's just really data systems—they all have their own nuances and components."

Some things haven’t changed, even with the new technologies. "Optimizing queries and moving data around have not gone away, and neither has the need to monitor and maintain these databases," said Lalonde. "And those 'schema-less' databases really do have schemas as it turns out—they're just more loosely defined."

As a result, Lalonde explained, the DBAs "have to have the same skills they've always had. Obviously modern DBAs have to be more flexible, understand a breadth of technologies, and work well in agile environments. What we look for in general is someone who really understands database fundamentals, because understanding those fundamentals translates across technologies well."

What's a DBA, anyway?

The shift in data technologies and how they're deployed hasn't just added more work onto the DBA's role—it has also redefined who fills it. With more of the operational tasks around databases shifting toward the operations side of "DevOps," the DBA role is much more closely linked to the application development process. And the skills usually associated with DBAs are now much more important across the development and operations teams.

"I believe automation has reduced the need for traditional Ops folks as they help scale the hardware and therefore the volume of queries... I believe automation allows for larger scale with fewer resources, but ultimately you still need an expert around."
"I think the roles of the developer, the DevOps guys, and data guy—maybe it's a data engineer, maybe a DBA, maybe a data scientist—those roles have to cope with a myriad of new technologies," said Gorman. "Each of those technologies has their own spectrum of maturity, features, and capabilities." And that means that each of those roles now requires at least some of the skills of a DBA.
Whoever ends up in the DBA role for these systems doesn't just need to have a general understanding of them—they need a much more nuanced understanding of what's going on inside their systems than they may have ever needed with relational databases. Just as the behavior of SQL queries can be tuned to some degree for each relational database, getting the best performance out of newer non-relational systems requires DBAs to have a deep understanding of their inner workings.

"How a database 'behaves' is highly dependent on a few choices that the developer of the database makes at the lowest levels," Lalonde said. "If you know what those choices are and how this particular database has made those choices, then you can get a good idea for how it will behave, generally speaking."

That's a level of familiarity that was once the domain of the most experienced database administrators and programmers. But just as data has become increasingly decentralized, it has spread the demands of the DBA role throughout the IT organization. Given how much time many people spend managing their personal stack of structured and unstructured data, we may all very well be DBAs at this point.

Percona Server failure after upgrade attempt

I was upgrading a MySQL server from MySQL 5.1 to MySQL 5.5 and then to Percona Server 5.6 on several replication slaves (in place upgrade). The upgrade to MySQL 5.5 worked fine but whenever I tried to upgrade to Percona Server 5.6, I kept seeing these assertion errors in the log file. It didn't happen every server but on several of them.

2016-08-03 15:57:15 10110 [Warning] InnoDB: Resizing redo log from 2*65536 to 2*131072 pages, LSN=25951778491874
2016-08-03 15:57:15 10110 [Warning] InnoDB: Starting to delete and rewrite log files.
2016-08-03 15:57:16 10110 [Note] InnoDB: Setting log file ./ib_logfile101 size to 2048 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
2016-08-03 15:57:20 10110 [Note] InnoDB: Setting log file ./ib_logfile1 size to 2048 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
2016-08-03 15:57:26 10110 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2016-08-03 15:57:26 10110 [Warning] InnoDB: New log files created, LSN=25951778491916
2016-08-03 15:57:26 7fc770a50740  InnoDB: Assertion failure in thread 140494565082944 in file pars0pars.cc line 865
InnoDB: Failing assertion: sym_node->table != NULL
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
21:57:26 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/

key_buffer_size=67108864
read_buffer_size=131072
max_used_connections=0
max_threads=100001
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 217676709 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x20)[0x893c80]
/usr/sbin/mysqld(handle_fatal_signal+0x348)[0x655d48]
/lib64/libpthread.so.0(+0xfeb0)[0x7fc76f5bfeb0]
/lib64/libc.so.6(gsignal+0x37)[0x7fc76ea2f237]
/lib64/libc.so.6(abort+0x148)[0x7fc76ea30578]
/usr/sbin/mysqld[0x9882ca]
/usr/sbin/mysqld[0x989946]
/usr/sbin/mysqld(_Z7yyparsev+0xb39)[0xa97a79]
/usr/sbin/mysqld[0x98af5e]
/usr/sbin/mysqld[0x98e1c3]
/usr/sbin/mysqld[0x9aed1b]
/usr/sbin/mysqld[0x9b075a]
/usr/sbin/mysqld[0x969f96]
/usr/sbin/mysqld[0x9d3697]
/usr/sbin/mysqld[0x92e6fa]
/usr/sbin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x41)[0x5a9cd1]
/usr/sbin/mysqld[0x6d6e20]
/usr/sbin/mysqld(_Z11plugin_initPiPPci+0x915)[0x6db165]
/usr/sbin/mysqld[0x5a0234]
/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x385)[0x5a3565]
/lib64/libc.so.6(__libc_start_main+0xf5)[0x7fc76ea1ba65]
/usr/sbin/mysqld[0x598075]
You may download the Percona Server operations manual by visiting
http://www.percona.com/software/percona-server/. You may find information
in the manual which will help you identify the cause of the crash.

The error message is obscure, after testing several different paths, I finally found one that worked.

Things I tried:

• shutting down with innodb_fast_shutdown  = 0
• Running check/repair on all tables
• Wiped out data and restored a copy of the data from the master.

What worked:

• innodb_fast_shutdown = 0 on MySQL 5.5.
• Deleted the innodb log files.
• Started MySQL again.
• Shutdown again with the fast shutdown, still on MySQL 5.5.
• Upgraded MySQL to Percona Server 5.6, using basic same my.cnf file.
• Started Percon Server 5.6. Then made changes to the my.cnf file and restarted again.

MySQL COALESCE() function

When I first became a DBA, I had a hard time understanding what the COALESCE function did and why you would ever use it.

If you read the explanation from documentations, it seems pretty straightforward:

"MySQL COALESCE() function returns the first non-NULL value of a list, or NULL if there are no non-NULL values." (lifted from here: http://www.w3resource.com/mysql/comparision-functions-and-operators/coalesce-function.php)

It wasn't too long time until I was writing a query for a database I had created myself where I needed the COALESCE function. I created a table that would pull data from a beta environment and a prod environment. I knew that when I was pulling beta data the prod values would always be NULL and when pulling the prod values, the beta values would always be NULL. So I needed to COALESCE the columns I was pulling in.


-- Example data:

CREATE DATABASE IF NOT EXISTS `beta`;

DROP TABLE IF EXISTS `beta`.`shards`;
CREATE TABLE `beta`.`shards` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shard_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`),
  KEY `idx_shard_name` (`shard_name`)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `beta`.`shards` ( `shard_name`) VALUES ('db2');


CREATE DATABASE IF NOT EXISTS `prod`;

DROP TABLE IF EXISTS `prod`.`shards`;
CREATE TABLE `prod`.`shards` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shard_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`),
  KEY `idx_shard_name` (`shard_name`)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `prod`.`shards` ( `shard_name`) VALUES ('db1');

CREATE DATABASE IF NOT EXISTS `analysis`;

DROP TABLE IF EXISTS `analysis`.`data_pull`;
CREATE TABLE `analysis`.`data_pull` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `external_id` varchar(70) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `schema_name` varchar(70) COLLATE utf8_unicode_ci NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `analysis`.`schema_sizes`;
CREATE TABLE `analysis`.`schema_sizes` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `schema_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'database name',
  `total_size_mb` decimal(12,3) unsigned NOT NULL COMMENT 'total size of data and index size calculated using is schema. Calculated independently of the the data_size column and index size column',
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `analysis`.`schema_sizes` (`id`, `schema_name`, `total_size_mb`, `date`) VALUES
(NULL, 'db1', '100','2016-07-22'),
(NULL, 'db2', '200','2016-07-22');

-- Example Query:

SELECT
COALESCE(c.id,c2.id) as external_id,
ss.schema_name,
ss.date
FROM schema_sizes ss
LEFT JOIN prod. shards c ON (ss.schema_name = c.shard_name)
LEFT JOIN beta. shards c2 ON (ss.schema_name = c2.shard_name)
GROUP BY ss.schema_name, ss.date;

This example is kinda simple but it the end results shows how the COALESCE pulls the id values from the beta and prod shard table so that I don't end up with NULL values in the external_id column.