Student questions are always interesting! They get me to think and to write. The question this time is: “How do I write a Bash Shell script to process multiple MySQL script files?” This post builds the following model (courtesy of Download MySQL Workbench) by using a bash shell script and MySQL script files, but there’s a disclaimer on this post. It shows both insecure and secure approaches and you should avoid the insecure (more...)
Here I am describing a simple scenario where we have a read replica (other than slave) which is currently pointing to master mysql instance.
We have another slave which is also pointing to same master. What we are going to see is, how to point read replica to new master after failover.
To make you familier with environment, we have 3 instances of mysql
mysql_a -> Current master mysql_b -> Current slave mysql_c -> read replica (more...)
While I previously blogged about installing Netbeans 8, some of my students would prefer to use the Eclipse IDE. This post shows how to install and configure Eclipse IDE, include the mysql-connector-java.jar, and write Java to access the MySQL.
You can download Eclipse IDE and then open it in Fedora’s Archive Manager. You can use the Archive Manager to Extract the Eclipse IDE to a directory of your choice. I opted to extract (more...)
Planning out my year, I decided to take the Oracle OCP and MySQL OCP exams. I checked for review books and was pleasantly surprised to find the soon to be released OCP MySQL Database Administrator Exam Guide (Exam 1Z0-883). However, I noticed that the book was actually prepared for the obsolete and discountinued Exams 1Z0-870, 1Z0-873, and 1Z0-874.
There isn’t an alternative review book for the OCP MySQL 5.6 Developer or Database Administrator (more...)
If you have multiple MySQL slaves or complex topology, its difficult to remember what slaves are replicating from which master.
We can go to slave servers and check “show slave status” which list master host, but if we want to find out other way, we have can run “show slave hosts” from master.
This will list down all the slaves that are getting replicated from this master.
Only problem I faced was, “show slave (more...)
This is a quick post on issues I faced while restoring a slave when GTID was enabled on master.
I have master created few days back and now I am trying to create a slave. I have GTID enabled on master.
root [mysql] >show master status G *************************** 1. row *************************** File: bin_log.000002 Position: 16682 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: f7718b80-c237-11e4-baa8-a0369f370a52:1-59 1 row in set (0.00 sec)
root [mysql] >show (more...)
This is a small article telling how to move a innoDB table from one MySQL server to another.
Moving MyISAM table from one server to another is very straight forward process. MyISAM table generates 3 files at file system level.
- .frm file contains table structure/definition
- .MYD file contains table data
- .MYI file contains index data
We can simply copy these 3 files to another MySQL server installation under desired database and it will show up (more...)
Using sysbench to performance test AWS RDS MySQL hardware is an easy three step operation.
Sysbench creates synthetic tests and they are done on a 1 mil row 'sbtest' table that sysbench creates in the MySQL database you indicate. The test doesn't intrude with your database schema, and it doesn't use your data, so it is quite safe. The test is an OLTP test trying to simulate event operations in the database as it (more...)
In this post I will talk about how to measure replication lag and check/fix replication inconsistencies with this tools:
I am using environment from previous blog post.
Master-Master replication with MariaDB 10.0.16 database on Debian 7.
Install Percona Toolkit on both nodes:
$ sudo wget percona.com/get/percona-toolkit.deb
There could be situations where we have to disable GTID in our replication setup.
We can follow below steps to do so. No need to change any GTID related parameters. We just have to change slave configuration and bounce slave. No need to touch master.
Step 1) Confirm that you are using GTID
If you check processlist on master, you can see GTID is enabled
master> show processlist G *************************** 1. row *************************** Id: (more...)
If we talk about MySQL replication, we always default to asynchronous replication. By asynchronous, we mean master will keep doing transactions while they are getting replicated to slave via IO thread asynchronously. In this replication, its possible that slave will fall behind but that does not impact master in anyway.
Problem with this replication is data-loss. Example: In case slave is falling behind and master host crashes, some of the transactions may not get transfered/replicated (more...)
Another method is to set up replication in both directions called master-master replication. But you must be aware that this setup brings some potential issues as data changes are happening on (more...)
Performance schema, as name suggest is a tool for monitoring MySQL performance. It was implemented staring from MySQL 5.5 and improved further in MySQL 5.6. Performance Schema includes set of tables that gives information on how database is performing. It provides a way to inspect internal execution of the server at run time. Performance schema monitors every “event” the server does that takes time and has been instrumented so that timing information can (more...)
ERROR 1205 (HY000) : Lock wait timeout exceeded; try restarting transaction
You get the error because your allocated time to hold a DML lock in a transaction exceeds the set limit. Usually the default limit to hold a DML row lock, set by innodb_lock_wait_timeout db parameter, is 50 seconds. If your transaction doesn't commit/rollback within 50 seconds (more...)
I’m taking a few weeks defocused from work, as a kind of grandpaternity leave. That said, the venue for my Dances of Infant Calming is a small-but-nice apartment in San Francisco, so a certain amount of thinking about tech industries is inevitable. I even found time last Tuesday to meet or speak with my clients at WibiData, MemSQL, Cloudera, Citus Data, and MongoDB. And thus:
1. I’ve been sloppy in my terminology around “geo-distribution”, in (more...)
I've never had a tool I really liked that would extract a chunk of a large production database for testing purposes while respecting the database's foreign keys. This past week I finally got to write one: rdbms-subsetter.
rdbms-subsetter postgresql://user:passwd@host/source_db postgresql://user:passwd@host/excerpted_db 0.001
Getting it to respect referential integrity "upward" - guaranteeing every needed parent record would be included for each child row - took less than a day. Trying to get it to also guarantee (more...)
A Pandas DataFrame has a nice to_sql(table_name, sqlalchemy_engine) method that saves itself to a database.
The only trouble is that coming up with the SQLAlchemy Engine object is a little bit of a pain, and if you're using the IPython %sql magic, your %sql session already has an SQLAlchemy engine anyway. So I created a bogus
PERSIST pseudo-SQL command that simply calls
to_sql with the open database connection:
%sql PERSIST mydataframe
The result is (more...)
PyOhio gave my lightning talk on ddlgenerator a warm reception, and Brandon Lorenz got me thinking, and PyOhio sprints filled my with py-drenaline, and now ddlgenerator can inspect your data and spit out SQLAlchemy model definitions for you:
$ cat merovingians.yaml
name: Clovis I
name: Childebert I
$ ddlgenerator --inserts sqlalchemy merovingians.yaml
from sqlalchemy import create_engine, Column, Integer, Table, Unicode
As part of my series on the keys to and likelihood of success, I outlined some examples from the DBMS industry. The list turned out too long for a single post, so I split it up by millennia. The part on 20th Century DBMS success and failure went up Friday; in this one I’ll cover more recent events, organized in line with the original overview post. Categories addressed will include analytic RDBMS (including data (more...)
Fre MySQL seminar on 27. august 2014 @ 13:00. Announcement by Oracle User Group Estonia:
Developing modern applications using MySQL.
In this seminar series learn how to best use MySQL for your existing and new development requirements with leading MySQL expert and Oracle Ace Director Ronald Bradford.
These presentations provide a detailed review of the essential lifecycle components for developing a successful software application and offer a checklist for your company to review the (more...)