Logicalwebhost Cheatsheet

mysql

8 January, 2011 (02:40) | mysql commands | By: unclecameron

First install the server. It will prompt you to enter a root password.

apt-get install mysql-server mysql-client

If you want a web-based “gui” manager for your server too, do:

apt-get install phpmyadmin

what it is what it does

create database somedatabasename;

creates a new database

create table bikes ( id int unsigned not null auto_increment primary key, make varchar(20), model varchar(20), part_name varchar(60), year varchar(20), thumper_pn varchar(20), mfr_pn varchar(30), description text, price varchar(15), image_path_and_name varchar(50), info_link varchar(60) ); example of how to fill up a table with stuff

delete from user where user='username'; deletes a user, almost easier than rename, just delete then and then re-add them using the grant command below

DELETE from USERTABLE where homedir = '/clients/somefolder/pdf/'; deletes some entry that fits the command

describe some_table;

shows what fields are in a table, you have to know the name of the table, which you can get with the describe command

drop database somedatabase;

deletes a database

grant all privileges on whateverdatabase.* TO 'whateveruser'@'localhost' identified by 'some_password' with grant option; adds a new user, change some_password to your password

mysql -u root -p

SET PASSWORD FOR root@localhost=PASSWORD('yournewpassword'); resets your mysql root password if you’re able to login to mysql first, meaning you already knew your password

mysqldump -u root -p somedatabase > somedatabase.sql backs up ‘somedatabase’ to somedatabase.sql

mysql -u root -p somedatabase < somedatabase.sql restores the database you just backed up

pv sqlfile.sql | mysql -u root -p dbname

restores a dumpfile, but shows you the progress if it's big and takes a long time

backup mysql database to REMOTE server

here we use mysqldump to back up a database to a remote server over ssh and in zipped (gzipped) format, which makes them MUCH smaller. Replace values below with what you're actually using:

mysqldump -u root --password=whatever sourcedatabasename | gzip -c | ssh remoteusername@remote.server.i.p "cat > /whatever/remote/machine/path/somefile.sql.gz"

reset root mysql password

/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &
mysql -u root (should just let you in)
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit
/etc/init.d/mysql stop
/etc/init.d/mysql start
mysql -u root -p (try your new password)

Fix corrupt db/table

If a mysql server gets cranky for some reason, it can eat a table in a database, or at least corrupt it during a read/write (like if the box gets rebooted). First try the native mysqlcheck utility like:

mysqlcheck -u root -p --repair --databases db_name

that should run through all your tables and try to fix them. If multiple db's are having problems, you can also run:

mysqlcheck -u root -p --repair --all-databases

If you get an error like:

Got error: 144: Table './yourdb/sometable' is marked as crashed and last (automatic?) repair failed when using LOCK TABLES

That kind of sucks. You can try to repair it like (obviously, change the db and table name to what they really are, not "yourdb"):

cd /var/lib/mysql/yourdb/

you should see 3 files like:

sometable.frm
sometable.MYD
sometable.MYI

before you start screwing with stuff, shut down mysql and then back those up somewhere:

/etc/init.d/mysql stop
cp sometable.* /home/yourfolder/wherever/

Now we do two things, the first commands will tell you if it thinks the table is corrupt or not, you want to run this on the MYI suffix file like:

myisamchk sometable.MYI
  Checking MyISAM file: ibf_posts.MYI
  Data records:       0   Deleted blocks:       0
  myisamchk: warning: Table is marked as crashed and last repair failed
  myisamchk: warning: 1 client is using or hasnt closed the table properly
  - check file-size
  myisamchk: warning: Size of indexfile is: 90492928      Should be: 1024
  - check record delete-chain 
  - check key delete-chain
  - check index reference
  - check data record references index: 1
  - check data record references index: 2
  - check data record references index: 3
  - check data record references index: 4
  - check data record references index: 5
  - check data record references index: 6
  - check data record references index: 7
  - check data record references index: 8
  - check record links
  myisamchk: error: Record-count is not ok; is 176228       Should be: 0
  myisamchk: warning: Found     181020 parts                Should be: 0 parts
  MyISAM-table 'sometable.MYI' is corrupted
  Fix it using switch "-r" or "-o"

This means that it really is corrupt, now let's hope it can be fixed. So run:

myisamchk -r -v sometable.MYI
  - recovering (with sort) MyISAM-table 'sometable.MYI'
  Data records: 0 
  - Fixing index 1
    - Searching for keys, allocating buffer for 116382 keys
    - Last merge and dumping keys
  - Fixing index 2
    - Searching for keys, allocating buffer for 110370 keys
    - Last merge and dumping keys
  - Fixing index 3
    - Searching for keys, allocating buffer for 74892 keys
    - Last merge and dumping keys
  - Fixing index 4
    - Searching for keys, allocating buffer for 43685 keys
    - Last merge and dumping keys
  - Fixing index 5
    - Searching for keys, allocating buffer for 33820 keys
    - Last merge and dumping keys
  - Fixing index 6
    - Searching for keys, allocating buffer for 91176 keys
    - Last merge and dumping keys
  - Fixing index 7
    - Searching for keys, allocating buffer for 110370 keys
    - Last merge and dumping keys
  - Fixing index 8
    - Searching for keys, allocating buffer for 39694 keys
    - Merging 4594107 keys
    - Last merge and dumping keys
    - Adding exceptions
  Data records: 176228

So it looks like it fixed it. To check this, run the earlier myisamchk command again like:

myisamchk sometable.MYI
  Checking MyISAM file: ibf_posts.MYI
  Data records:  176228   Deleted blocks:       0
  - check file-size
  - check record delete-chain
  - check key delete-chain
  - check index reference
  - check data record references index: 1
  - check data record references index: 2
  - check data record references index: 3
  - check data record references index: 4
  - check data record references index: 5
  - check data record references index: 6
  - check data record references index: 7
  - check data record references index: 8
  - check record links

This means it worked, so now start mysql and see if your database works, you shouldn't get that mysql error when you start it again:

/etc/init.d/mysql start
  Starting MySQL database server: mysqld.
  Checking for corrupt, not cleanly closed and upgrade needing tables..

« linux commands

Nagios sytem monitoring Howto »