How to reset MySQL Root Password in Linux

 

First, Let’s stop the mysql service, In Red Hat’s family (Red Hat Enterprise, Fedora, CentOS) the Mysql Daemon is called ‘mysqld‘, but in Debianlike Ubuntu based distributions and OpenSuse its called ‘mysql‘. I’ll be using the debian based naming
sudo service mysql stop
OR
sudo /etc/init.d/mysql stop
Now, we’ll start the server in safe mode and skip grant tables so that it wont ask for the password we had lost when we try to login Continue reading “How to reset MySQL Root Password in Linux” »
Share

Importing Big mysqldump with Progress Bar PV

I am using CentOS 6.4 box, so it requires me to install EPEL repo at the first place:

$ rpm -Uhv http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

Install pv using yum:

$ yum install -y pv

Installation done. Let the importing begin!

$ pv /home/user/my_big_dump.sql | mysql -uroot -p

For Ubuntu/Debian distribution intall PV using-

apt-get install pv

 

Share

MySQLDump Shell script for backup

You can use the following script to dump mysql database and can add to crontab entry for daily backup!

#!/bin/sh

DAY=`/bin/date +%Y%m%d`
TFILE="/backup/MYDB.$DAY.gz"
mysqldump -u root -p'MYPASSWORD' MYDB | gzip > $TFILE
echo "cache dump completed, dump script by rumi (hasan.emdad@mango.com.bd)"

Share

MySQL Root Password Reset

First things first. Log in as root and stop the mysql daemon. Now lets start up the mysql daemon and skip the grant tables which store the passwords.

mysqld_safe –skip-grant-tables

You should see mysqld start up successfully. If not, well you have bigger issues. Now you should be able to connect to mysql without a password.

mysql –user=root mysql
update user set Password=PASSWORD('new-password') where user='root';
flush privileges;
exit;

Now kill your running mysqld, then restart it normally. You should be good to go. Try not to forget your password again.
 

Share

MySQL Master-Master or Master-Slave Replication Error Fix

Scenario Master – Master replication

MasterA is a client facing server
MasterB is a warm standby server (read only)

MasterB restarted abruptly and when instances were braught back up MasterA (it’s slave) was showing the following error:

MasterA has the following error in show slave status:

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

Solution:

Slave: stop slave;

Master: flush logs

Master: show master status; — take note of the master log file and master log position

Slave: CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.00000X′, MASTER_LOG_POS=106;

Slave: start slave;

Read more: http://mysqlpreacher.com/wordpress/2010/12/could-not-find-first-log-file-name-in-binary-log-index-file/#ixzz2ILRnl4XI

Src: http://mysqlpreacher.com/wordpress/2010/12/could-not-find-first-log-file-name-in-binary-log-index-file/

Share

Monitor MySQL database restore progress with pv

The pv command is one that I really enjoy using but it's also one that I often forget about. You can't get a much more concise definition of what pv does than this one:

pv allows a user to see the progress of data through a pipeline, by giving information such as time elapsed, percentage completed (with progress bar), current throughput rate, total data transferred, and ETA.

The usage certainly isn't complicated:

To use it, insert it in a pipeline between two processes, with the appropriate options. Its standard input will be passed through to its standard output and progress will be shown on standard error.

A great application of pv is when you're restoring large amounts of data into MySQL, especially if you're restoring data under duress due to an accidentally-dropped table or database. (Who hasn't been there before?) The standard way of restoring data is something we're all familiar with:

# mysql my_database < database_backup.sql

The downside of this method is that you have no idea how quickly your restore is working or when it might be done. You could always open another terminal to monitor the tables and databases as they're created, but that can be hard to follow.

Toss in pv and that problem is solved:

# pv database_backup.sql | mysql my_database
96.8MB 0:00:17 [5.51MB/s] [==> ] 11% ETA 0:02:10

When it comes to MySQL, your restore rate is going to be different based on some different factors, so the ETA might not be entirely accurate.

Src: http://rackerhacker.com/2010/11/24/monitor-mysql-restore-progress-with-pv/

Share

MySQLTuner adjust your MySQL database

MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. Within seconds, it will display statistics about your MySQL installation and the areas where it can be improved.

It's key to remember that MySQLTuner is a script which can assist you with your server, but it is not the solution to a badly performing MySQL server. The best performance gains come from a thorough review of the queries sent to the server, and an evaluation of the MySQL server itself. A qualified developer in your application's programming or scripting language should be able to work with a MySQL database administrator to find improvements for your server. Once the server and application are optimized well, you may need to consider hardware upgrades to the physical server itself.
This is a really useful tool for helping to optimize MySQL performance.Understanding the various my.cnf variables and how they affect performance can seem really complicated but this tool takes some of the pain away and makes it easier to understand the effects of each variable.It is especially useful to be able to see the global memory usage, memory usage per thread and the maximum possible memory usage — that is really valuable information that is otherwise complex to calculate.

Run MySQLTuner in Debian

Copy and past the below code and save it as mysqltuner.pl using nano 😉 Continue reading “MySQLTuner adjust your MySQL database” »

Share

MySQL Master-Master Replication with Heartbeat

Setting Up The Two Base Systems

In this setup there will be one master server (which runs the ISPConfig control panel interface) and one slave server which mirrors the web (apache), email (postfix and dovecot) and database (MySQL) services of the master server.

To install the clustered setup, we need two servers with a Debian 6.0 minimal install.

Install only steps 1 – 8 of the perfect server tutorial and not the other steps as they differ for a clustered setup!

In my example I use the following hostnames and IP addresses for the two servers:

Master Server

Hostname: server1.example.tld
IP-address: 192.168.0.105

Slave server

Hostname: server2.example.tld
IP-address: 192.168.0.106

Whereever these hostnames or IP addresses occur in the next installation steps you will have to change them to match the IP's and hostnames of your servers.

It is a good idea to synchronize the system clock with an NTP (network time protocol) server over the Internet. Simply run Continue reading “MySQL Master-Master Replication with Heartbeat” »

Share

MySQL Database Import Command

The syntax is as follows to import the data created by mysqldump command:

mysql -u {DB-USER-NAME} -p {DB-NAME} < {db.file.sql} mysql -u {DB-USER-NAME} -h {MySQL-SERVER-HOST-NAME} -p {DB-NAME} < {db.file.sql}

In this example import a file called sales.sql for salesdb1 user and sales db, enter:

$ mysql -u sales -p salesdb1 < sales.sql

If database salesdb1 does not exists, first create it and than import it as follows:

$ mysql -u root -p -e 'create database salesdb1'
$ mysql -u sales -p salesdb1 < sales.sql

Share

MySQL Cheat Sheet

Selecting a database:

mysql> USE database;

Listing databases:

mysql> SHOW DATABASES;

Listing tables in a db:

mysql> SHOW TABLES;

Describing the format of a table:

mysql> DESCRIBE table;

Creating a database:

mysql> CREATE DATABASE db_name;

Creating a table:

mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));
Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);

Load tab-delimited data into a table:

mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;
(Use \n for NULL)

Inserting one row at a time:

mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');
(Use NULL for NULL)

Retrieving information (general):

mysql> SELECT from_columns FROM table WHERE conditions;
All values: SELECT * FROM table;
Some values: SELECT * FROM table WHERE rec_name = "value";
Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";

Reloading a new data set into existing table:

mysql> SET AUTOCOMMIT=1; # used for quick recreation of table
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;

Fixing all records with a certain value:

mysql> UPDATE table SET column_name = "new_value" WHERE record_name = "value";

Selecting specific columns:

mysql> SELECT column_name FROM table;

Retrieving unique output records:

mysql> SELECT DISTINCT column_name FROM table;

Sorting:

mysql> SELECT col1, col2 FROM table ORDER BY col2;
Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC; Continue reading “MySQL Cheat Sheet” »

Share