Keeping things safe and within MySQL now:
Keeping things safe and within MySQL now:
Below are 10 common replication problems :
1)Session binlog :
Setting sql_log_bin = 0 will disable binary logging for the session. So any DML/DDL query executed within that session on the master will not be replicated to slave
Turn off binary logging on master :
mysql> set sql_log_bin = 0 ; Query OK, 0 rows affected (0.00 sec)
Create table in reptest database ( on master )
mysql> create table reptest(ID int) ; Query OK, 0 rows affected (0.01 sec) mysql> show tables ; +-------------------+ | Tables_in_reptest | +-------------------+ | reptest | +-------------------+ 1 row in set (0.00 sec)
Validate to see that table is not created on slave :
mysql> use reptest ; Database changed mysql> show tables ; Empty set (0.00 sec)
2) Updating slave directly
Master and slave will get out of …[Read more]
Below are some of the common scenarios where information_schema is useful :
How to check the database size using information_schema ?
SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;
How to list the tables created before 4 days ?
select * from information_schema.tables where CREATE_TIME < NOW() - INTERVAL 4 DAY ;
Columns table :
List all the tables that have a column called birth_date
select table_name from information_schema.columns where table_schema='employees' and column_name='birth_date';
List all tables without primary/unique key:
select table_schema,table_name from information_schema.columns group by table_schema,table_name having sum(if(column_key in ('PRI','UNI'), …[Read more]
Daniel was tracking down what appeared to be a networking problem….
- server reported 113 (No route to host)
- However, an strace did not reveal the networking stack ever returning that.
- On the other side, IP packets were actually received.
- When confronted with mysteries like this, I get suspicious – mainly of (fellow) programmers.
- I suggested a grep through the source code, which revealed return -EHOSTUNREACH;
- Mystery solved, which allowed us to find what was actually going on.
- Don’t just believe or presume the supposed origin of an error.
- Programmers often take shortcuts that cause grief later. I fully appreciate how the above code came about, but I still think it was wrong. Mapping a “similar” situation onto an existing …
You got a production support call related to connection usage. Max_connections is set to 400 and connection usage has reached the maximum limit.
Below are some of the customized reports that can be used to trace the problem
Count by host :
mysql> select count(*),host from information_schema.processlist group by HOST; +----------+-----------+ | count(*) | host | +----------+-----------+ | 1 | localhost | +----------+-----------+ 1 row in set (0.03 sec)
Count by database:
mysql> select count(*),host from information_schema.processlist group by DB; +----------+-----------+ | count(*) | host | +----------+-----------+ | 1 | localhost | +----------+-----------+ 1 row in set (0.00 sec)
Count by username:
mysql> select count(*),user from information_schema.processlist group by user; …
Dear MySQL Users,
The MySQL Workbench team is announcing availability of the first
release of its upcoming major product update, MySQL Workbench 6.2.
MySQL Workbench 6.2 focuses on support for innovations released
5.6 and MySQL 5.7 DMR (Development Release) as well as MySQL Fabric 1.5,
with features such as:
* A new spatial data viewer, allowing graphical views of result
containing GEOMETRY data and taking advantage of the new GIS
capabilities in MySQL 5.7.
* Support for new MySQL 5.7.4 SQL syntax and configuration options.
* Metadata Locks View shows the locks connections are blocked or waiting
* MySQL Fabric cluster connectivity – Browsing, view status, and connect
to any MySQL instance in a Fabric Cluster.
* MS Access migration Wizard – easily move to MySQL Databases.
Other significant usability …[Read more]
recently while working on MySQL cluster 7.3.5, we came across this error
Forced node shutdown completed. Caused by error 2341: 'Internal program error (failed ndbrequire)(Internal error, programming error or missing error message, please report a bug). Temporary error, restart node'.
The management nodes would start up but
the data nodes would complete phase 1 & shutdown, some times at phase 2 and some times at phase 5.
The error one would see in the trace file would be as follows:
Status: Temporary error, restart node Message: Internal program error (failed ndbrequire) (Internal error, programming error or missing error message, please report a bug) Error: 2341 Error data: CREATE_TABLE_REF Error object: NDBCNTR (Line: 2493) 0x00000003 Program: ndbd
How did we resolve it ?
First, few of our data nodes had an incorrect connect-string
Second, we had pre-assigned NodeGroups for the …[Read more]
There are several ways to upgrade MySQL. In this post, we will use a combination of shell scripts and the mysqldump application to export our MySQL data, and then re-import it back into the upgraded version of MySQL.
In this example, we will be doing a minor version upgrade. We will be going from 5.6.17 to 5.6.19. This method may not work if you are upgrading from one major release to another – from 5.1 to 5.5, or 5.5 to 5.6. You will want to check each version and review the new features/functions and also what features/functions have been deprecated. We are also assuming that no one will be using the database during the time it takes for us to do the upgrade.
If you want to upgrade from a …[Read more]
The MySQL Utilities Team is pleased to announce the latest GA release of MySQL Utilities, release-1.4.4. This release includes improvements in terms of usability, stability, security and an overall improvement regarding the comprehensibility of the provided error messages.
The following highlights a few of the more significant improvements.
- mysqlrpladmin errant transactions for switchover have been improved. Note : The errant transactions check requires all servers in the topology to have GTID’s enabled.
- Failed MySQL server connection error messages now display the actual error returned from the failed connection.
- Error messages that occur during automatic slave discovery (–discover-slaves-login) have been improved. Now the –verbose option generates specific information for each slave, including their respective errors.
- mysqlserverinfo utility can now start …