LAMPdocs: Linux, Apache, MySQL, PHP

Recently I had to deal with a Directadmin VPS that had mysql installed, but for some reason there wasn’t a record for root in mysql user table. Here are the queries I used to add a root account, and to assign all the necessary privilegies.

First of all We need to create a record with username and password.

INSERT INTO user(user, password) values (‘root’, PASSWORD(‘PASSWORD_HERE’));

Then we need to add all the privileges to our root:

UPDATE `mysql`.`user` SET `Select_priv` = ‘Y’,
`Insert_priv` = ‘Y’,
`Update_priv` = ‘Y’,
`Delete_priv` = ‘Y’,
`Create_priv` = ‘Y’,
`Drop_priv` = ‘Y’,
`Reload_priv` = ‘Y’,
`Shutdown_priv` = ‘Y’,
`Process_priv` = ‘Y’,
`File_priv` = ‘Y’,
`Grant_priv` = ‘Y’,
`References_priv` = ‘Y’,
`Index_priv` = ‘Y’,
`Alter_priv` = ‘Y’,
`Show_db_priv` = ‘Y’,
`Super_priv` = ‘Y’,
`Create_tmp_table_priv` = ‘Y’,
`Lock_tables_priv` = ‘Y’,
`Execute_priv` = ‘Y’,
`Repl_slave_priv` = ‘Y’,
`Repl_client_priv` = ‘Y’,
`Create_view_priv` = ‘Y’,
`Show_view_priv` = ‘Y’,
`Create_routine_priv` = ‘Y’,
`Alter_routine_priv` = ‘Y’,
`Create_user_priv` = ‘Y’ WHERE `user`.`Host` = ” AND `user`.`User` = ‘root’ LIMIT 1 ;

And the last command:

GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY ‘PASSWORD_HERE’ WITH GRANT OPTION

Then you can do the same for da_admin mysql user:

GRANT ALL PRIVILEGES ON *.* TO da_admin@localhost IDENTIFIED BY ‘PASSWORD_HERE’ WITH GRANT OPTION;

Restart mysql and have fun.

I have found a way to prevent server overload because of MySQL. The tip seems to be really good, and I am giving the link to the original post. Here it is: Dramatically Improve MySQL Performance

In order to make your heavy MySQL servers perform faster all you need is to add a line to your MySQL configuration file: my.cnf.

max_write_lock_count = 1

This line should be added under [mysqld] section of my.cnf. I won’t describe what does this setting actually do as you’re welcome to read it in the original post. I just want to say that this little string greatly increases your MySQL server performance. You’re welcome to try and to get back in your comments.

Today I’m just sharing a link: I don’t think I can explain this better than IBM guys did. These three acrticles contain a comprehensive guide for your LAMP system optimization. Here is the first one, that contains general linux settings for perfect LAMP config. The second one describes how to fine tune Apache and PHP and finally the third one deals with MySQL – you will find out how to optimize MySQL configuration for any server . I usually recommend this guide for those who didn’t tune anything before – it’s clear and brings excellent results. If you have links to similar excellent tutorials, you’re welcome to share them in comments. Have a nice day!

Today I will show a simple command that will allow you to restore a database from a previously made sql file. No matter, how the backup was done, if it has sql commands, you will restore it in the fastest possible way.

If your database backup file exceeds 10 megabytes, it’ll be very hard to restore it using usual panel options (I mean Directadmin, Cpanel, PhpMyAdmin, etc). If you have shell a access to your server, you may significally decrease the time necessary for backup restore. All you have to do id to upload this backup to an accessible location, and then to run a command:

mysql -ppassword -u user database_name -h localhost < path_to_sql_file

This command will start mysql database restore ptocess without prompting for a password. If you want to enter your password each time, you should leave -p option empty.

This is the fastest way to recreate your database using a dump file.