Things I need to remember

Working with mySQL, there are certain things that you do now and again. Sometimes you need a list over things to remember.

To get remote access to a database and table, log in to mysql with:

mysql -u root -p

Then, change the privileges:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'secret_password' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit;

After that we need to edit the /etc/mysql/my.cnf-configuration file. If this is not there *[mysqld] add it, and add a line with the bind address:

[mysqld]
bind-address=192.168.0.100 # The IP address of the host running mysql

Lastly, we need to restart the mySQL server:

sudo /etc/init.d/mysql restart

Then you should have access to the database from your client computer.

If not try:

sudo mysqld_safe --skip-grant-tables

Or:

sudo su -
mysql -u root -p

and then:

GRANT ALL PRIVILEGES on *.* to 'root'@'localhost' IDENTIFIED BY '<password>';
FLUSH PRIVILEGES;

Search and Replace

UPDATE tbl_fi_batteries SET region = REPLACE(region, '14 - Häme', '14 - Häme');

Delete a table

DROP TABLE <tablename>