MySQL replication will stop if an error occurs when running a query on the slave. The reason is so you can resolve the problem, thus keeping the data consistent with the master. You can skip those errors if you know those queries and why they are failing. Skip one query mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; [...]

This comes up in discussions almost every new project I work on, because it’s a very important thing to consider when designing a database. When deciding when and how to create an index in your MySQL database, it’s important to consider how the data is being used. Let’s say you have a database of employees. [...]

How about backing up all the databases in the server? That’s an easy one, just use the –all-databases parameter to backup all the databases in the server in one step. mysqldump –all-databases> alldatabases.sql How to Backing up only the Database Structure in MySQL You can backup only the database structure by telling mysqldump not to [...]

#!/bin/bash # Shell script to backup MySql database # To backup Nysql databases file to /backup dir and later pick up by your # script. You can skip few databases from backup too. # ——————————————————————– # This is a free shell script under GNU GPL version 2.0 or above # Copyright (C) 2004, 2005 nixCraft [...]

Sometimes, when you have got a large number of tables in your database and while taking the dump of that particular database, you would have encountered this strange error mysqldump: Got error: 1016: Can’t open file: ‘.\database\certain_table.frm’ (errno: 24) when using LOCK TABLES There are two solutions to avoid this error 1. Set the following [...]

Today working on a client project, a shell script which uses MySQL to dump the database, has got me into this error mysqldump: Got error: 1045: Access denied for user   I was typically amazed getting this error because all my configurations were correct. I have created proper users to access MySQL database and correctly [...]

mysqlreport makes a friendly report of important MySQL status values. mysqlreport transforms the values from SHOW STATUS into an easy-to-read report that provides an in-depth understanding of how well MySQL is running. mysqlreport is a better alternative (and practically the only alternative) to manually interpreting SHOW STATUS. When I was trying to use mysqlreport under [...]

I already wrote about how to move or migrate user accounts from old Linux / UNIX server to a new server including mails and home directories. However, in reality you also need to move MySQL database which may host your blog, forum or just your data stored in MySQL database. The mysqldump command will only [...]

Applications that perform a lot of memory accesses (several GBs) may obtain performance improvements by using large pages due to reduced Translation Lookaside Buffer (TLB) misses. HugeTLBfs is memory management feature offered in Linux kernel, which is valuable for applications that use a large virtual address space. It is especially useful for database applications such [...]

Usually you run mysqldump to create a database copy and backups as follows: $ mysqldump -u user -p db-name > db-name.out Copy db-name.out file using sftp/ssh to remote MySQL server: $ scp db-name.out user@remote.box.com:/backup Restore database at remote server (login over ssh): $ mysql -u user -p db-name < db-name.out OR $ mysql -u user [...]