Linux MySQL Commands

To restart mysql service, type as root user:
service mysqld restart

If not root use sudo or su to become a root:

su - service mysqld restart
If sudo is configured
sudo /sbin/service mysqld restart

Stop mysqld:

service mysqld stop
Start mysqld:
service mysqld start

MySQL Command Line Examples

Passing parameters into a SQL script:

Shell Script
mysql -e "set @firstname:='$1'; set @lastname='$2'; source run.sql;" 
MySQL Script
insert into customers (firstname, lastname) values (@firstname, @lastname); 

Database maintenance:
Analyze and Optimize all databases

mysqlcheck -u username -p -ao --all-databases

OR, for optimizing a single database:

MySQL Update Examples

Remove a weird character from data in a field

UPDATE table_name SET field_name = REPLACE(field_name, '\r', '') 

This says to replace all '\r' characters in the field data with nothing ('')

Trim trailing spaces from field data

UPDATE table_name SET field_name = RTRIM(field_name) 


MySQL Users and Privileges

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON databasename.* TO username@localhost;

Or combine these into one statement if the user doesn't exist yet:


GRANT ALL PRIVILEGES ON databasename.* TO username@localhost IDENTIFIED by 'password';

Get list of users:

select * from mysql.user;

Change root password:

 mysqladmin -u root -p'oldpassword' password 'NEWPASSWORD'


Subscribe to RSS - MySQL