Advertisements

Change or Set the MySQL Root password (Windows):

Change or Set the MySQL Root password (Windows):
================================================

1. Stop your MySQL server completely. This can be done by accessing the Services
window inside Windows XP and Windows Server 2003, where you can stop the MySQL service.

2. Open your MS-DOS command prompt using “cmd” inside the Run window.
Inside it navigate to your MySQL bin folder, such as C:\MySQL\bin using the cd command.

3. Execute the following command in the command prompt: mysqld.exe -u root –skip-grant-tables

4. Leave the current MS-DOS command prompt as it is, and open a new MS-DOS command prompt window.

5. Navigate to your MySQL bin folder, such as C:\MySQL\bin using the cd command.

6. Enter “mysql” and press enter.

7. You should now have the MySQL command prompt working.
Type “use mysql;” so that we switch to the “mysql” database.

8. Execute the following command to update the password:

UPDATE user SET Password = PASSWORD(‘NewPassword’) WHERE User = ‘root’;

However, you can now run any SQL command that you wish.

After you are finished close the first command prompt and type “exit;” in the second command prompt
windows to disconnect successfully. You can now start the MySQL service.

Please note that the 8 step process above can differ depending on the MySQL version you are using,
how you configured your server, etc. However, many times you can still easily
work around a problem that you experience in any of the steps.

To change root password to an empty:
====================================

UPDATE user SET Password = PASSWORD(“”) WHERE User = ‘root’;

use mysql;
update user set password=null where User=’root’;
flush privileges;
quit;

Advertisements

How to Check and Repair MySQL Tables Using Mysqlcheck

How to Check and Repair MySQL Tables Using Mysqlcheck
=====================================================

Database Name : sms
Table Name: attend

1. Check a Specific Table in a Database

mysqlcheck -c sms attend -u root -p

2. Analyze Tables using Mysqlcheck

# mysqlcheck -a sms attend -u root -p

3. Check All Tables and All Databases

# mysqlcheck -c -u root -p –all-databases

4. Optimize Tables using Mysqlcheck

# mysqlcheck -o sms attend -u root -p

5. Repair Tables using Mysqlcheck

# mysqlcheck -r sms attend -u root -p

SELECT table_name as name, table_rows as rows FROM information_schema.tables as t1 WHERE table_rows > 0

select * from (show table status like ‘%attend%’) as t1;

LIKE ‘attend’ G

MySQL Bin Files Eating Lots of Disk Space

MySQL Bin Files Eating Lots of Disk Space
==========================================

I get a large amount of bin files in the MySQL data directory called “server-bin.n”
or mysql-bin.00000n, where n is a number that increments.

[root@ENMDB1 mysql]# df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/sda5 9.7G 8.2G 1.1G 89% /var

Solution:
========

We need to periodically RESET MASTER or PURGE MASTER LOGS to clear out the old logs.

For Safe side I taken backup of the main log files:
—————————————————
[root@ENMDB1 mysql]# cp slow-queries.log /mysqldb/DBBACKUP/slow-queries.log_bak
[root@ENMDB1 mysql]# cp mysql-query.log /mysqldb/DBBACKUP/mysql-query.log_bak

Purge BINARY LOGS Logs (leaving December BINARY LOGS)
Note: For retaing current month log files purge to mysql-bin.000469 (Nov 30 05:08 mysql-bin.000469).

mysql> PURGE BINARY LOGS TO ‘mysql-bin.000469’;
Query OK, 0 rows affected (40.85 sec)

[root@ENMDB1 mysql]# df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/sda5 9.7G 888M 8.3G 10% /var

The binary log has two important purposes:

Data Recovery : It may be used for data recovery operations.
After a backup file has been restored, the events in the binary
log that were recorded after the backup was made are re-executed.
These events bring databases up to date from the point of the backup.

High availability / replication : The binary log is used on master
replication servers as a record of the statements to be sent
to slave servers. The master server sends the events contained
in its binary log to its slaves, which execute those events to
make the same data changes that were made on the master.