Category: MySQL

Mysql Tips and tricks mostly used in Linux

Linux, MySQL

Repairing MySQL InnoDB databases

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 …

Linux, MySQL, Security

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 …

Linux, MySQL

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: …

Bash, Linux, MySQL

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 …

Linux, MySQL, Security, Systemd, Wordpress

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 …

Linux, MySQL

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 …

Linux, MySQL

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

Linux, MySQL

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 …

Linux, MySQL

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 …

Linux, MySQL

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 …

Linux, MySQL

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 …

Linux, MySQL

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 …

MySQL

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 ...

MySQL

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 …

MySQL

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 …

MySQL

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.

MySQL

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 …