Backing Up MySQL Database as a Restricted User

As a developer, you might find yourself in a situation where you need to back up a MySQL database, but you don't have full administrative privileges. While it's typically a sysadmin's job, knowing how to handle this can save the day. This guide provides a workaround for a common issue where restricted users encounter errors when attempting to dump databases.

Understanding the Error

When using mysqldump as a user with limited privileges, you might encounter the following error:

mysqldump: Couldn't execute 'show events': Access denied for user 'user'@'some-host' to database 'dbname' (1044)

This error occurs because the user doesn't have the necessary privileges to access database events. Fortunately, there's a straightforward solution.

Solution: Skipping Events

If you don't need to back up the events or can't alter your user privileges, you can instruct mysqldump to skip events altogether.

For MyISAM Databases:

1. Open your terminal or command prompt.

2. Run the following command:

mysqldump -u username -p --skip-events --databases dbname > dbname_dump.sql

• Replace username with your MySQL username.
• Replace dbname with the name of your database.
• When prompted, enter your MySQL password.

This command will back up your database, excluding events, into a file called dbname_dump.sql.

For InnoDB Databases:

InnoDB databases support transactions, and it's often desirable to use the --single-transaction option for a consistent backup.

1. Open your terminal or command prompt.

2. Run the following command:

mysqldump -u username -p --skip-events --single-transaction --databases dbname > dbname_dump.sql

• Replace username with your MySQL username.
• Replace dbname with the name of your database.
• When prompted, enter your MySQL password.

The --single-transaction option is particularly useful for live systems where locking the database (which happens by default) would interfere with ongoing operations.

Conclusion

Understanding how to navigate around permission issues when performing backups is a handy skill. By using the --skip-events and --single-transaction options, you can effectively back up a MySQL database even as a restricted user. Remember, always ensure you have the necessary permissions to perform backups and consult with your sysadmin when in doubt. Handling data responsibly is paramount, and backups are a critical part of data management.

Configure MySql and ODBC Connector In Windows 2012 Server

Installing MySql and its ODBC Connector In the Windows 2012 Server

Download The Microsoft Visual C++ 2010 Redistributable
http://www.microsoft.com/en-gb/download/details.aspx?id=14632

Download and install the ODBC Connector
http://cdn.mysql.com/Downloads/Connector-ODBC/5.3/mysql-connector-odbc-5.3.4-winx64.msi

Downoading the MySQL
http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.26-winx64.zip

c:\mysql>cd bin
c:\mysql\bin>mysqld --install
Service successfully installed.

Go to Services and start the Mysql Services.

c:\mysql\bin>mysql -u root -p
>>No password is needed for first time

Set the root Password
=====================
mysql>
mysql> UPDATE mysql.user SET password=PASSWORD('redhat') WHERE user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | localhost |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)


mysql> delete from mysql.user where user='';
Query OK, 1 row affected (0.00 sec)

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)


mysql> create database fileserver;
Query OK, 1 row affected (0.01 sec)

mysql>GRANT ALL PRIVILEGES ON fileserver.* TO 'fileserver'@'localhost' IDENTIFIED BY 'fileserver';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql>

Mysql Cluster Using Mysql NDB

Mysql Cluster using NDB(Network DataBase) provides a self healing mysql Cluster which provides a good performance. Mainly the Mysql Cluster Contains 3 Components ie using Management , SQL and Data parts. 

Here we will be configuring two Management and two Data/SQL (together in One server) for the HA. Once the configuration is completed we will have two end points to connect to te database so we need to keep an Load balancer in front of the SQL end points.

OS used is RHEL7
Selinux Enabed
Firewall Disabled

Management Server## Perform the Following steps in both the Management Server's. 

Install Needed Packages 

=================

yum install glibc.i686  ncurses-libs.i686 libstdc++.i686 libgcc.i686 -y


Make Directories and Download the Cluster Files
====================================

mkdir /usr/src/mysql-mgm
cd /usr/src/mysql-mgm
wget http://cdn.mysql.com/Downloads/MySQL-Cluster-7.4/mysql-cluster-gpl-7.4.7-linux-glibc2.5-i686.tar.gz
tar zxvf mysql-cluster-gpl-7.4.7-linux-glibc2.5-i686.tar.gz

cd mysql-cluster-gpl-7.4.7-linux-glibc2.5-i686
cp bin/ndb_mgm* /usr/bin/
chmod 755 /usr/bin/ndb_mgm*


mkdir /var/lib/mysql-cluster
vi /var/lib/mysql-cluster/config.ini
==========================================
[NDBD DEFAULT]
NoOfReplicas=2
DataMemory=80M
IndexMemory=18M
[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]
DataDir=/var/lib/mysql-cluster
[TCP DEFAULT]

# Section for the cluster management node
[NDB_MGMD]
NodeId=1
# IP address of the first management node (this system)
HostName=192.168.70.130

[NDB_MGMD]
NodeId=2
#IP address of the second management node
HostName=192.168.70.131

# Section for the storage nodes
[NDBD]
# IP address of the first storage node
HostName=192.168.70.132
DataDir= /var/lib/mysql-cluster
[NDBD]
# IP address of the second storage node
HostName=192.168.70.133
DataDir=/var/lib/mysql-cluster
# one [MYSQLD] per storage node
[MYSQLD]
[MYSQLD]
==========================================

chown mysql. /var/lib/mysql-cluster -R

To start the Management Service
========================
ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster/

Data And SQL Server#Perform this on both of the Server's
==============================================

Install the needed Packages
====================
yum install libaio.i686 libaio-devel.i686 -y
yum install perl -y
yum -y install perl-Data-Dumper

Download the packages
cd /usr/local/
wget http://cdn.mysql.com/Downloads/MySQL-Cluster-7.4/mysql-cluster-gpl-7.4.7-linux-glibc2.5-i686.tar.gz
tar zxvf mysql-cluster-gpl-7.4.7-linux-glibc2.5-i686.tar.gz
mv /root/mysql-cluster-gpl-7.4.7-linux-glibc2.5-i686.tar.gz mysql
chown mysql. mysql -R
cd mysql

Initializing the database
scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data

cp support-files/mysql.server /etc/init.d/
chmod 755 /etc/init.d/mysql.server

cd /usr/local/mysql/bin
mv * /usr/bin
cd ../

vi /etc/my.cnf
============
[mysqld]
ndbcluster
# IP address of the cluster management node
ndb-connectstring=192.168.70.130,192.168.70.131
[mysql_cluster]
# IP address of the cluster management node
ndb-connectstring=192.168.70.130,192.168.70.131
============

mkdir /var/lib/mysql-cluster

cd /var/lib/mysql-cluster
ndbd --initial
/etc/init.d/mysql.server start

After this, secure the MySQL installation by running the appropriate script:

/usr/local/mysql/bin/mysql_secure_installation


Testing 
In the Management Node check the command ndb_mgm and check the status

Mysql Engine Swap MyISam to InnoDB and InnoDB to MyISam

To convert all tables in a database from InnoDB to MyISAM, run the MySQL following command, replacing db_name with the database name in question:


mysql -e "SELECT concat('ALTER TABLE ', TABLE_NAME,' ENGINE=MYISAM;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'"

Then, after stopping MySQL, you want to get the ibdata* and ib_logfiles out of the way:

Code:
/etc/init.d/mysql stop
mkdir -p /root/innodb.bak
mv ib* /root/innodb.bak/
/etc/init.d/mysql start

Now you've got MySQL started up with the tables using MyISAM, and it's time to get them converted back to InnoDB, fingers crossed (again replace db_name with your database name):

mysql -e "SELECT concat('ALTER TABLE ', TABLE_NAME,' ENGINE=InnoDB;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name' AND ENGINE = 'MyISAM'"

Mysql error : Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'))

Error creating issue: Could not create workflow instance: root cause: while inserting: [GenericEntity:OSWorkflowEntry][id,null][name,jira][state,0] (SQL Exception while executing the following:INSERT INTO OS_WFENTRY (ID, NAME, INITIALIZED, STATE) VALUES (?, ?, ?, ?) (Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'))

 

Cause This is required by MySQL:
Statement based binlogging does not work in isolation level READ UNCOMMITTED and READ COMMITTED since the necessary locks cannot be taken.

 

Resolution
To change to row based binary logging, set the following in /etc/my.cnf (or your my.cnf if it's elsewhere):

binlog_format=row

Mysql HA with Haproxy and Master-Master replication

Once Mysql master -master replication is done we set HA with those using HAproxy

http://enekumvenamorublog.wordpress.com/2014/06/25/mysql-replication-master-master/

HAProxy

yum install haproxy

To use Haproxy with MYsql we need to create a user in mysql so that haproxy can access it .

GRANT ALL PRIVILEGES ON *.* TO 'haproxy'@'192.168.216.180' IDENTIFIED BY '';

Sample configuration  /etc/haproxy/haproxy.cfg

global
log 127.0.0.1 local0 notice
user haproxy
group haproxy

# turn on stats unix socket
stats socket /var/lib/haproxy/stats mode 777

defaults
log global
retries 2
timeout connect 1000
timeout server 5000
timeout client 5000
listen stats 192.168.255.180:80
mode http
stats enable
stats uri /stats
stats realm HAProxy\ Statistics
stats auth admin:password

listen MYSQL 192.168.255.190:3306
balance source
mode tcp
option mysql-check user haproxy
server controller1 192.168.216.130 check
server controller2 192.168.216.135 check
[root@HAPROXY ~]#

Mysql replication-Master-Master

MySQL Master-Master replication.

Master-1 my.cnf configuration:

mkdir /var/lib/mysql/log/
log-bin=/var/lib/mysql/log/mysql-bin
log_warnings
log_slow_queries = /var/lib/mysql/log/slow.log
long_query_time = 5
log_long_format
tmpdir = /tmp
server-id = 1
log_slave_updates
replicate-same-server-id = 0
auto_increment_increment = 10
auto_increment_offset = 1
relay-log = mysql-relay-bin

Master-2 my.cnf configuration:
mkdir /var/lib/mysql/log/
log-bin=/var/lib/mysql/log/mysql-bin
log_warnings
log_slow_queries = /var/lib/mysql/log/slow.log
long_query_time = 5
log_long_format
tmpdir = /tmp
server-id = 2
replicate-same-server-id = 0
auto_increment_increment = 2
auto_increment_offset = 2
relay-log = mysql-relay-bin

First setup Master1 as Master and Master2 as slave for Master-1:

Follow below steps:
On Master-1:

grant replication slave on *.* to 'root'@'192.168.216.135' identified by 'admin';
show master status;

It shows file name and position, Use these records on Master-2 to run it as slave for Master-1.
Step 3: Now log on to master-2 and run the below query:

CHANGE MASTER TO MASTER_HOST='192.168.216.130', MASTER_USER='root',MASTER_PASSWORD='admin', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=106;

Step 4: start slave
Step 5: show slave status \G

On this status, the following 2 records should be as follows
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

These 2 records indicates Replication status. If these parameters show “Yes” that means replication is running successfully.

Setup Master2 as Master and Master1 as slave for Master-2:

On Master-2 server:

grant replication slave on *.* to 'root'@'192.168.216.130' identified by 'admin

Step 2: mysql> show master status;

Step 3: Now log on to master-1 and run the below query:
CHANGE MASTER TO MASTER_HOST='192.168.216.135', MASTER_USER='root',MASTER_PASSWORD='admin', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=346;
Step 4: start slave
Step 5: show slave status \G

The following parameters should show “Yes”, so that replication is running successfully

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

On both servers “slave_IO_Running” and “slave_SQL_Running” parameters should always be “Yes” for successful Master-Master Replication.

Recovering from InnoDB Corruption in MySQL

InnoDB is a robust storage engine for MySQL, but like any system, it can encounter issues such as corruption. Understanding how to address these problems is crucial for database administrators. This guide will outline steps to recover from an InnoDB corruption using the innodb_force_recovery option.

Understanding the Error

The error log indicates an assertion failure within InnoDB, suggesting data corruption. Typical causes include hardware faults, sudden shutdowns, or bugs. The error message suggests steps for recovery and forces InnoDB into a special "recovery" mode.

130306 22:02:18 mysqld_safe Number of processes running now: 0
130306 22:02:18 mysqld_safe mysqld restarted
130306 22:02:18 [Note] Plugin 'FEDERATED' is disabled.
130306 22:02:18 InnoDB: The InnoDB memory heap is disabled
130306 22:02:18 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130306 22:02:18 InnoDB: Compressed tables use zlib 1.2.3
130306 22:02:18 InnoDB: Using Linux native AIO
130306 22:02:18 InnoDB: Initializing buffer pool, size = 128.0M
130306 22:02:18 InnoDB: Completed initialization of buffer pool
130306 22:02:18 InnoDB: highest supported file format is Barracuda.
130306 22:02:18 InnoDB: 5.5.30 started; log sequence number 1629186928
130306 22:02:18 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130306 22:02:18 [Note] - '0.0.0.0' resolves to '0.0.0.0';
130306 22:02:18 [Note] Server socket created on IP: '0.0.0.0'.
130306 22:02:18 [Note] Event Scheduler: Loaded 0 events
130306 22:02:18 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.30-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
130306 22:02:19 InnoDB: Assertion failure in thread 47204348393792 in file trx0purge.c line 840
InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
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.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
03:02:19 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.

Steps to Recovery

1. Stop mysqld:

First, ensure that MySQL is not running to prevent further data corruption.

service mysqld stop

2. Backup Critical Files:

Before proceeding with any recovery steps, back up the existing InnoDB data files.

mkdir /root/mysql_backup
cp /var/lib/mysql/ib* /root/mysql_backup/

3. Configure MySQL for Recovery:

Edit the MySQL configuration file (/etc/my.cnf) and add the following under the [mysqld] section:

innodb_force_recovery = 4

There are different levels of innodb_force_recovery you can use, from 1 to 6. Levels 1-4 are generally considered safe, while 5 and 6 are used as a last resort.

4. Restart mysqld:

Restart the MySQL server. It will start in recovery mode due to the innodb_force_recovery setting.

service mysqld start

5. Dump All Tables:

Create a dump of all databases. This might take some time depending on the size of your databases.

mysqldump -A > /root/mysql_backup/dump.sql

6. Drop Corrupted Databases:

Identify and drop the corrupted databases. If you're unsure which ones are corrupted, you might need to drop all databases and restore them from the dump later.

mysql -u root -p -e "DROP DATABASE corrupted_db"

7. Stop mysqld:

Once again, stop the MySQL server to proceed with the next steps.

service mysqld stop

8. Remove InnoDB Data Files:

Delete the old InnoDB data files. Be cautious with this command – ensure you have backups!

rm /var/lib/mysql/ib*

9. Comment out innodb_force_recovery:

Edit /etc/my.cnf and comment out or remove the innodb_force_recovery option.

10. Restart mysqld:

Start the MySQL server. It should recreate the InnoDB data files.

service mysqld start

11. Restore Databases:

Restore the databases from the dump you created earlier.Copy code

mysql < /root/mysql_backup/dump.sql

12. Repair and Optimize:

Finally, check and repair all databases and tables.

mysqlcheck –all-databases –repair
innodb force recovery options

Mode 1 - Doesn't crash MySQL when it sees a corrupt page
Mode 2 - Doesn't run background operations
Mode 3 - Doesn't attempt to roll back transactions
Mode 4 - Doesn't calculate stats or apply stored/buffered changes
Mode 5 - Doesn't look at the undo logs during start-up
Mode 6 - Doesn't roll-forward from the redo logs (ib_logfiles) during start-up
1 (SRV_FORCE_IGNORE_CORRUPT)
Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump 
over corrupt index records and pages, which helps in dumping tables.
2 (SRV_FORCE_NO_BACKGROUND)
Prevent the main thread from running. If a crash would occur during the purge operation, 
this recovery value prevents it.
3 (SRV_FORCE_NO_TRX_UNDO)
Do not run transaction rollbacks after recovery.
4 (SRV_FORCE_NO_IBUF_MERGE)
Prevent insert buffer merge operations. If they would cause a crash, do not do them. 
Do not calculate table statistics.
5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.
6 (SRV_FORCE_NO_LOG_REDO)
Do not do the log roll-forward in connection with recovery.
The database must not otherwise be used with any nonzero value of innodb_force_recovery. 
As a safety measure, InnoDB prevents users from performing INSERT, UPDATE, or 
DELETE operations when innodb_force_recovery is greater than 0.

**Hint : A simple query for finding all of your InnoDB tables in case you want to specifically target the corruption.
SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'innodb';
\