First install the server. It will prompt you to enter a root password.
apt-get install mysql-server mysql-client |
apt-get install mysql-server mysql-client
If you want a web-based “gui” manager for your server too, do:
apt-get install phpmyadmin |
apt-get install phpmyadmin
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
what it is |
what it does |
create database somedatabasename; |
create database somedatabasename;
|
creates a new database |
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 </td |
|
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; |
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 |
|
login to mysql as user root |
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</code |
restores the database you just backed up |
pv sqlfile.sql | mysql -u root -p dbname |
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" |
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) |
/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 |
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 |
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 |
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/ |
cd /var/lib/mysql/yourdb/
you should see 3 files like:
sometable.frm
sometable.MYD
sometable.MYI |
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/ |
/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" |
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 |
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 |
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.. |
/etc/init.d/mysql start
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
2