When you forgot the password to your MySQL server, you can reset it using the following steps. This will require a downtime since you are going to stop MySQL service. After this, remember to keep you mysql root password.
How to reset the root password for mysql
Follow these steps to reset root password:
1. service mysql stop
2. mysqld_safe --skip-grant-tables &
3. mysql -u root
4. mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit
5. service mysql stop
6. service mysql start
7. mysql -u root -p
Make mysql listen to all addresses
By default, MySQL database server will only answer queries from the localhost. In most cases where the mysql database server is on a remote machine, mostly on production environments where the web server is separate from the database server or you need to grant a remote user to access the database server, you need to set MySQL to listen to all (or specific) addresses.
1. sudo netstat -nap | grep mysql
2. sudo vi /etc/mysql/my.conf
3. Edit the bind-address line to: bind-address = 0.0.0.0
4. sudo service mysql restart
5. sudo mysql -u root -p
6. mysql> GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'password';
Creating a user and assigning database to a user
1. Login as root.
2. CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'userpassword';
3. GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
4. FLUSH PRIVILEGES;
Skip mysql error and start the slave:
mysql> SET GLOBAL sql_slave_skip_counter = 1 ;
mysql> START SLAVE ;
Select the creation time and update time of all tables on a database, oldest updated first
mysql> SELECT create_time, UPDATE_TIME, TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables where table_schema='test' ORDER BY create_time, UPDATE_TIME asc, TABLE_SCHEMA, TABLE_NAME;
Select the tables on a database and show the sizes in MB, biggest first
mysql> SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) "MB" FROM information_schema.TABLES WHERE table_schema = "sarah" order by MB desc;
– masterkenneth