Update a MySQL table from the command line (find and replace)

I thought this might be helpful to some of you out there, as I routinely have to do this myself. Need to update a table, or a lot of rows of data in MySQL? Here is a handy Find and Replace SQL script to run at the command line.

mysql> update TABLENAME set FIELDNAME  = replace(FIELDNAME, 'X','Y');

In the above example, X is the existing text and Y is the new text you want to replace with. Have fun!

How to export your MySQL database using mysqldump

Use your command line to backup your mysql database by exporting it to an .sql file. All you need to complete this task is shell access & a mysql account.

A major use for this command is to easily migrate your sites from one server to another. Since this is often part of a larger process, I like to decide ahead of time which directory will contain this file. So I’ll start by navigating to my desired directory and scoping it out:
[code]user@server:~$ cd /path/to/dir/; ls -al[/code]
Now that you’re sure about using this directory. Here is the basic mysqldump command:
[code]user@server:/path/to/dir$ mysqldump db_name > db_backup.sql[/code]
But, depending on your level of access, you may need to modify it:
[code]user@server:/path/to/dir$ mysqldump -u mysql_user -p db_name > db_backup.sql
Enter password:[/code]
After entering your mysql password, a file called db_backup.sql will be created in the default location, which is your current directory (three cheers for planning ahead!).
Of course, we could have just as easily added a location statement within the command:
[code]user@server:~$ mysqldump -u mysql_user -p db_name > /path/to/dir/db_backup.sql[/code]
But, you’re going to want to cd there anyways, just to verify that everything’s in order 🙂

Be sure to browse our related posts describing how to get migrate files from one server to another, and how to import an .sql file into an empty MySQL databse.

Create a MySQL Database, Username, Password, and Privileges from the command line

Step 1: Login to MySQL ( you will need an account )
[code]user@server:~$ mysql -u mysql_user -p
Enter password:[/code]

Step 2: Create the Database
[code]mysql > create database db_name;[/code]

Step 3: Verify that it’s there
[code]mysql > show databases;[/code]

Step 4: Create the User
[code]mysql > create user db_user;[/code]

Step 5: Grant privileges while assigning the password
[code]mysql > grant all on db_name.* to ‘db_user’@’localhost’ identified by ‘db_password’;[/code]
*Note: The localhost field usually doesn’t have to be edited, but you can set it to the specific address.

The above example grants all privileges, obviously. But you will likely want to limit privileges under many circumstances. These parameters include select, insert, and delete.
Choose all that apply and separate by comma:
[code]mysql > grant select, insert, delete, update on db_name.* to ‘db_user’@’localhost’ identified by ‘db_password’;[/code]