Introduction: This post is a copy of the wonderful following post: https://blackbird.si/mysql-corrupted-innodb-tables-recovery-step-by-step-guide/https://blackbird.si/mysql-corrupted-innodb-tables-recovery-step-by-step-guide/ Here are some important exerts from it: MySQL – Corrupted InnoDB tables recovery – Step by step guide Posted in Databases By Alen Krmelj On March 19, 2013, 5-6 minutes InnoDB tables don’t get corrupted easily, but when they do, it usually happens …
Category: MySQL
Mysql Tips and tricks mostly used in Linux
Resetting MySQL/MariaDB root password in Ubuntu 16.04/18.04
Introduction: In case you have forgotten the ‘root’ password in MySQL/MariaDB(10.0.x) you can reset the password as follows: Ref: https://kofler.info/root-login-problem-mit-mariadb/ STEPS: Stop the currently running MySQL/MariaDB service mysql stop Start MySQL/MariaDB in non-protected mode: mysqld_safe –skip-grant-tables –skip-networking & Login as root in MySQL/MariaDB mysql -u root Set the new root password: For MySQL Previous to …
Switch database type from H2 to MySQL in Atlassian Jira
Introduction: After having tested Jira and decided to keep it for production it is very recommended to change the type of database used by Jira. The default database at delivery time is H2(local file dB) and in this HOW-TO I describe what I had to do to execute that switch under Debian Jessie. Steps: References: …
Display MySQL databases types in bash
Based on the site: http://stackoverflow.com/questions/213543/how-can-i-check-mysql-engine-type-for-a-specific-table Here is a bash command that will display the databases types(innoDB or MyISAM) of all the MySQL databases except for the defaults ones(mysql, etc) for i in $(mysql -u root -p -e “show databases;” | egrep -v ‘Database|information_schema|mysql|performance_schema’); do echo “——————–$i——————–“; mysql -u root -p -e “use $i; show table …
Resolving Mysql error: Too many open files
Introduction: As I upgraded from Mysql 5.5 to 5.6 suddenly some sites were showing the following error: …… Too many open files The issue has to do with the present limitations given to the system and PAM system to open max 1024 files. After doing some research I found this site below here which is …
Resetting MariaDB root password in Ubuntu 16.04 LTS
Introduction: Becasue it’s such a good article and don’t want to lose it, this following article is a full copy (with maybe some minor changes) taken from this site: http://tecadmin.net/steps-to-reset-mariadb-root-password-in-linux/# Step 1: Stop MariaDB Service First we need to stop MariaDB service using following command. # /etc/init.d/mysql stop Shutting down MySQL. [ OK ] Step …
Create a new database and use in MySQL/MariaDB
These commands will create a new database in MySQL/MariaDB including a new user/password with full access to the DB. mysql -p -u root Enter the MySQL root password. mysql> CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_bin; mysql> GRANT ALL PRIVILEGES ON dbname.* TO ‘user’@’localhost’ IDENTIFIED BY ‘password’; mysql> FLUSH PRIVILEGES; mysql> QUIT
Resetting MySQL passwords in Linux Debian/Ubuntu
In Linux Debian/Ubuntu an extra user debian-sys-maint which also has all the access rights is used for maintenance. It can also be used to reset users passwords especially the root password when lost. Here are some tips regarding resetting them. Resetting the ‘root’ passwordmysqladmin -u root -p password “MyNewPass”ORmysql -u debian-sys-maint -p update mysql.user set …
PAM-Mysql user authentication in Ubuntu 14.04 LTS Server
Introduction: As I was wanting to set-up a cluster of web servers based on Apache2 and fcgi I realized that I didn’t want to have to create/delete/update each individual fcgi user in each web server. Therefore I decided to authenticate the fcgi users through MySQL (in fact MariaDB). I’ve done that many years back but …
Rebuild MySQL replication slave LIVE from master
Problem: You have 2 MySQL servers in the configuration Master ==>> Slave and the slave has gone very badly out of sync with the master. Normally it’s no real problem if you can lock the master tables, make a dump of the databases of the master and import them back into the slave. But the …
logrotate /usr/bin/mysqladmin: refresh failed; error: ‘Unknown error’
Problem: After a fresh install of Debian Squeeze and mysql server, started to appear the following error as email from cron: /etc/cron.daily/logrotate: /usr/bin/mysqladmin: refresh failed; error: ‘Unknown error’ error: error running shared postrotate script for ‘/var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log ‘ run-parts: /etc/cron.daily/logrotate exited with return code 1 Cause: After the installation of mysql-server package, the first …
Mysql Loop replicating in Debian Squeeze
I have been doing replicating from master to Slave for quite a while and sometimes found it a bit limited especially when the master in down and ‘writes’ need to be done to the databases. Here is a method where no matter which server is down you can always write to it and the changes …
Flushing mySQL Binary logs
In order to keep the disk usage of the binlogs down while having replication, it is necessary to periodically perform a PURGE BINARY LOGS command. Please keep in mind that it is important to ensure that you don’t remove any binlogs that are still needed by the replicas. For example: PURGE BINARY LOGS BEFORE ‘2008-04-02 …
Running a second instance of mysqld
Sometimes it’s needed to have 2 instances of mysql daemon in the same server. In that case I decided to use the port 3307 for the second instance. Although there are other (maybe even easier) ways to do that, here is one of them. Creating a second instance of MySQL server on port 3307. Create …
Various MySQL useful commands
Repairing all MySQL tables mysqlcheck –repair –extended –all-databases ————- Dumping the content of all databases in SQL format. (can be used for backup/migration purposes) mysqldump -p –user=root –add-drop-table –all-databases > all_my_databases.sql Import all databases from above saved file mysql -u root -p < all_my_databases.sql ------------- Dumping the content of a single database in SQL format.(can ...
Retrieving lost MySQL root password
It often happens. You are given the job of administrating a MySQL server where the root password was lost. What to do? Here is the solution I found in Internet at the link below. Thanks for the useful tip. http://www.cyberciti.biz/tips/recover-mysql-root-password.html by Vivek Gite · 121 comments You can recover MySQL database server password with following …
Tool to unstick MySQL Replicators.
# I’m managing an infrastructure with a number of databases who # (for codified reasons that I cannot influence) suffer from this situation often. # So, I’ve written a cron script to manage the situation. # Does anyone see any foreseeable issues with this logic (see below)? # —————————————– SCRIPT —————————————– #!/bin/bash ## Tool to …
Slave rebuild from live slave.
It is also possible to set up a slave by dumping an existing slave of the master. To do this, use the following procedure on the existing slave: 1.Stop the slave’s SQL thread and get its current status: mysql> STOP SLAVE SQL_THREAD; mysql> SHOW SLAVE STATUS; 2.From the output of the SHOW SLAVE STATUS statement, …
PROBLEM: Could not find target log during relay log initialization
SOLUTION: delete relay bin file in slave and try slave stop ; CHANGE MASTER TO master_log_file=’name_of_current_file_on_master’,master_log_pos=4; slave start; it will download play again the file IMPORTANT: Make sure the master log file is the one that was being read by the slave when the error occured, otherwise you loose data.
FULL Manual rebuilding of SLAVE MySQL server mysql02 from Master mysql01
On mysql01 (MASTER): ——————– mysqldump -u root -p –single-transaction –flush-logs –master-data=2 –all-databases –delete-master-logs | gzip | ssh mysql02 ‘cat | gunzip >/var/lib/mysql_temp.sql’ echo “SHOW MASTER STATUS;” | mysql -p -u root Note the filename eg. mysql-bin.000299 we will use it later on mysql02 On mysql02 (SLAVE): ——————- – Changed the following config is in /etc/mysql/my.cnf …
MySQL replication issues
Good links: =========== http://www.howtoforge.com/how-to-repair-mysql-replication MySQL recover from Backup .sql data ====================================== mysql -u root -p dbName backup-dbNameDump.sql ———– Very useful in slave ————————————————————– slave-skip-errors=126,1062 126 = the ambiguous ‘unknown error on master’ typically caused by tables in dis-repair on master. 1062 = duplicate entry (happens all the time on high capacity db in master-master setup)
MYSQL tools debian packages
maatkit and innotop maatkit as package is dependant to a perl module present in system maatkit as tar.gz can be installed independantly by a normal user