Perfect way to reset MySQL Root Password on Debian/Ubuntu

If you forgot your MySQL root password, you can reset it by following these steps.

1. Stop the MySQL service.

service mysql stop

2. Start MySQL without password and permission checks.

mysqld_safe --skip-grant-tables &

3. Press [ENTER] again if your output is halted.

4. Connect to MySQL.

mysql -u root mysql

5. Run following commands to set a new password for root user. Substitute NEW_PASSWORD with your new password.

UPDATE user SET password=PASSWORD('NEW_PASSWORD') WHERE user='root';
FLUSH PRIVILEGES;

6. Restart the MySQL service.

service mysql restart
Share

Reset a MySQL root password for Debian

Use the following steps to reset a MySQL root password by using the command line interface.

Stop the MySQL service
(Ubuntu and Debian) Run the following command:

sudo /etc/init.d/mysql stop

(CentOS, Fedora, and Red Hat Enterprise Linux) Run the following command:

sudo /etc/init.d/mysqld stop

Start MySQL without a password
Run the following command. The ampersand (&) at the end of the command is required. Continue reading “Reset a MySQL root password for Debian” »

Share

Reinstalling MySQL on CentOS/Redhat 6

Some time we faces issues with MySQL installation on Linux machine. If we simply remove MySQL packages and re-install doesn’t fixes the issue, in that case old settings may still exists on server which again affects new install. In that case first uninstall MySQL completely from system and erase all settings of old install. To do the same follow the below settings.

Note: Please do not use below steps if MySQL have any running databases.

Step 1: Uninstall MySQL Packages
First uninstall all the MySQL packages installed on your server

# yum remove mysql mysql-server

Step 2: Romove MySQL Directory
Now we need to remove MySQL data directory from system which by default exists at/var/lib/mysql. If you didn’t find this, It may be changed to some other place, which you can find in my.cnf file with variable datadir. Delete the /var/lib/mysql directory from system but we prefer to rename it to keep a backup of existing files.

# mv /var/lib/mysql /var/lib/mysql_old_backup Continue reading “Reinstalling MySQL on CentOS/Redhat 6” »

Share

MySQL Master-Master Replication

This article is about setting up MySQL Master-Master database replication between two Cloud Servers. Master-Master data replication allows for replicated data, stored on multiple computers, to be updated by any authorized contributing member of the group. This allows for more open collaboration than Master-Slave replication where any needed changes identified by a group member must to be submitted to the designated “master” of the node.

The operating system we will use is Debian 5 (Lenny).

Setup Outline

  • We will have two Cloud Servers, named debian501 and debian502 for the purpose of this exercise. Both servers have two IP addresses (one public, one private). We will configure the replication to be done over the private IP interface so that we don’t incur any bandwidth charges.

Installing MySQL

  • First we need to install MySQL on both the Debian Cloud Servers. As always, prior to installing any packages, we need to make sure that our package list is up to date and our locale/language settings are configured properly.
  • Update the package database:
 #aptitude update
  • Install locales (optional):
 #aptitude install locales #dpkg-reconfigure locales
  • The dpkg-reconfigure locales command will bring up a locales setting window where you can choose the locales for your system depending on your country and region. In this case we have chosen en_GB.UTF-8.
  • Now, you can run the following commands to install MySQL:
 #aptitude install mysql-server mysql-client libmysqlclient15-dev

Continue reading “MySQL Master-Master Replication” »

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

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

Recover MySQL root Password

You can recover MySQL database server password with following five easy steps.

Step # 1: Stop the MySQL server process.

Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password.

Step # 3: Connect to mysql server as the root user.

Step # 4: Setup new mysql root account password i.e. reset mysql password.

Step # 5: Exit and restart the MySQL server.

Here are commands you need to type for each step (login as the root user):

Step # 1 : Stop mysql service

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &
Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root
Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended
[1]+  Done                    mysqld_safe --skip-grant-tables

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start
# mysql -u root -p

Share

How to Allow MySQL Client to Connect to Remote MySQL server

By default, MySQL does not allow remote clients to connect to the MySQL database.

If you try to connect to a remote MySQL database from your client system, you will get “ERROR 1130: Host is not allowed to connect to this MySQL server” message as shown below.

$ mysql -h 192.168.1.8 -u root -p
Enter password:
ERROR 1130: Host '192.168.1.4' is not allowed to connect to this MySQL server

You can also validate this by doing telnet to 3306 mysql port as shown below, which will also give the same “host is not allowed to connect to this mysql server” error message as shown below. Continue reading “How to Allow MySQL Client to Connect to Remote MySQL server” »

Share

Recover MySQL root Password

 

Step # 1 : Stop mysql service
# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &
Output:[1] 5988 Starting mysqld daemon with databases from /var/lib/mysql mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root
Output:Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:
# /etc/init.d/mysql stop
Output:
Stopping MySQL database server: mysqld STOPPING server from pid file /var/run/mysqld/mysqld.pid mysqld_safe[6186]: ended [1]+ Done mysqld_safe –skip-grant-tables

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start
# mysql -u root -p

Src: http://www.cyberciti.biz/tips/recover-mysql-root-password.html

Share

MySQL Tidbits

Allow Slave to connect master:

C:\mysql>create user 'replica'@'192.168.0.110' identified by '<YOUR PASSWORD>';

NOTE: 192.168.0.110 which is the SLAVE machine‟s address.

i. mysql>grant create,insert,select,update,delete on ejbca.* to 'replica'@'192.168.0.110';

Checking if Slave can connect to Master:

C:\>mysql –u replica –h 192.168.0.100 –p

C:\>Enter password: <YOUR VALUES>

NOTE: If the mysql prompt opens then the Master machine can be accessed from the Slave machine.

 

 

Share