| Author: | John M. Gabriele |
|---|---|
| Date: | May 2007 |
| Back to: | homepage |
Just some quick notes on MySQL that I'm jotting down.
MySQL has its own notion of "users", separate from those entries in your /etc/passwd file. What makes it slightly confusing is that there's a MySQL "root" user, just as there's a root user on your OS.
The user information for a given db is stored in the special database named "mysql". More on manipulating that db below.
To install:
apt-get install mysql-client mysql-server
Note: you may be able to install various versions (4.0 or 5.0) depending upon what version of Debian you're running. The above command on Etch gives me 5.0.
Access the mysqld server using the client like so:
mysql -u root -p
Debian Etch (maybe Sarge too?) uses a mysql user called debian-sys-maint for start/stop and cron scripts. There will actually be 2 other mysql users already set up, both named "root" -- one with Host set to "localhost", the other with Host set to whatever your hostname is.
Upon install, Debian will create a new system user, as well as a new group, both named "mysql". This user owns all the mysql-related files, such as the data in /var/lib/mysql. (If you install your own mysql in /opt, your data will instead be in /opt/mysql/data.)
To control mysqld use /etc/init.d/mysql start|stop|restart|reload|status. That command will take care of calling mysqladmin and mysqld_safe for you. The mysql user owns the mysqld process. Even though root starts the process (via /etc/init.d/mysql), the --user=mysql option gets automatically passed to the mysqld command for you.
You also may want to install something like phpMyAdmin (apt-get install phpmyadmin) to manipulate your databases/tables using a GUI interface via your web browser.
When you apt-get install mysql on Debian, most everything is all set up for you automatically. On older versions of Debian, the mysql root password may not be set upon install configuration. To set it manually:
/usr/bin/mysqladmin -u root password 'enter-your-good-new-password-here'
As the Debian mysql-server readme says, "If you already had a password set add " -p " before "-u" to the line above." If you've lost the mysql root password, then see below for how to re-set it.
If you like, you can use a different directory than /var/lib/mysql to keep your databases data in (say, /opt/data).
I got that tip about the ib_logfiles from http://blog.robinz.info/archives/2006/03/01/how-to-move-mysql-datadir/.
Here's some commands you might find yourself issuing at the mysql> prompt:
show status;
show databases;
show tables in mysql;
describe mysql.user;
create database some_db;
use some_db;
select user(), database(), version();
show tables;
describe some_table;
create table some_table (id int auto_increment primary key,
name varchar(30),
date_created datetime,
color enum('red','green','blue'),
quantity int not null);
alter table people change column fave_color favorite_color varchar(20);
alter table people add column favorite_flavor varchar(20),
add column favorite_flower varchar(20);
insert into some_table (name, date_created, color, quantity)
values ('John', now(), 'green', 15);
insert into a_table (foo, bar, baz) values
(1, 2, 'corncob'), (2, 4, 'pine cone');
select col_1, another_col from some_table where another_col < 100;
delete from some_table where quantity < 10;
update some_table set quantity = 30 where name = 'John';
update some_table set color = 'blue'
where quantity >= 20 and quantity < 30;
select * from people where id > 50 order by lucky_number limit 10;
select * from people where name like '%lark%'; # Eg, "Clark Kent".
select id, name, pow(quantity, 2) from some_table;
select count(*) from some_table where color = 'green';
select min(quantity) from some_table;
select max(quantity) from some_table where color = 'blue';
select avg(quantity) from some_table;
select sum(quantity) from some_table;
select * from some_table where quantity < ( select avg(qty) from some_table );
select users.id, users.name, orders.date_created
from users, orders
where users.id = orders.user_id;
drop table some_table;
drop database some_db;
exit;
Note:
Use the mysqldump command:
mysqldump -u username -p --opt some_db > some_db.sql
mysqldump -p --opt some_db some_table
mysqldump -p --opt --all-databases | ssh other_machine "cat - > all_db_bkup.sql"
mysqldump -p --opt --databases mysql some_db another_db | \
ssh other_machine "cat - > few_db_bkup.sql"
You can just dump the structure of a table, if you like:
mysqldump -p --opt --no-data some_db > table_structure.sql
To restore:
mysql -u somebody -ppa55w3rd db_name < some_db.sql
Or, you might do something like this:
mysqladmin -h 'other_hostname' create db_name mysqldump --opt db_name | mysql -h 'other_hostname' db_name
or even
mysqldump -uSRC_USER -pSRC_PWD -a --databases DATBASE1 DATABASE2 DATABASE3 | \
ssh TARGET_USER@TARGET_HOST "mysql -uTARGET_USER -pTARGET_PWD"
$ mysqladmin status $ mysqladmin version mysql> show status; mysql> show variables; mysql> show processlist;
Depending upon your privileges, you can kill a process, say process #3, like so:
mysql> kill 3
MySQL user data is stored in the database named "mysql":
grant all on some_db.* to 'your_mysql_name'@'your_client_host' identified by 'pa55wrd';
grant all on some_db.some_tbl to da_man identified by 'wa11awa11a';
grant select on *.* to pasquale identified by 's3cr3t';
/usr/bin/mysqladmin -u root password 'funkyfresh44'
set password for root@localhost=password('funkyfresh');
set password for root@myhome=password('funkyfresh');
# Remove any anonymous users:
delete from mysql.user where User='';
delete from mysql.db where User='';
flush privileges;
Note, when you see mysql.db or *.*, they're talking about some_db.some_table.
The grant command actually adds a mysql user, as well as setting privileges for that mysql user. If you manually change the mysql db (not using the grant command), then you'll need to execute "flush privileges" afterwards.
# /etc/init.d/mysql stop # /usr/bin/mysqld_safe --skip-grant-tables & # mysql -u root mysql> GRANT ALL PRIVILEGES ON * to root@localhost IDENTIFIED BY 'newpwd'; mysql> FLUSH PRIVILEGES; mysql> quit; # mysqladmin shutdown # /etc/init.d/mysql start
You can do that "flush" step from the command-line if you like:
mysqladmin -h localhost flush-privileges
Alternatively, the MySQL docs say that, instead of the GRANT command above, you can use:
UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';