MySQL
From TechWiki
Learning SQL - the language used for interacting with databases - by example. For general stuff on databases, see the DataBases section
Official on-line manual: MySQL 5.0 Manual.
Recommended literature:
"Teach Yourself SQL in 10 Minutes"; Sams Publishing; 2000; link
Contents
|
Admin
Root password
mysqladmin -u root password xxxx
Character Codes
Note that sometimes you need to tell MySQL about the character encoding, especially if its UTF-8 and not latin-1. Start MySQL from command line with:
mysql --default-character-set=utf8 -uname -ppwd -hhost
Backups
Dump Database
mysqldump -u root -p dbname > dbname.sql mysqldump -u root -p dbname tablename > dbname.sql mysqldump -u root -p --all-databases > all.sql
Note that you can use MySQL queries in a dump
mysqldump -u root -p dbname --where='where_condition' > name.sql
Dump Database as CSV File
mysqldump -hhost -uroot -p --tab=/Temporary/dir --fields-terminated-by=, --lines-terminated-by=\n DATABASE TABLE
creates a file called TABLE.csv containing the database schema and TABLE.txt containing the CSV data into /Temporary/dir.
Note that the second file is created by the user mysql, so if it complains about being able to create the file, it's probebly the permissions. So do
chmod 777 /Temporary/dir
Also note that sometimes it is better to omit --lines-terminated-by=\n and get new lines by default if your machine gets confused with new-line delimiters and sticks everything on one line.
Restore database
mysqladmin -u root -p create dbname mysql -u root -p dbname < dbname.sql
More on backups here. Note that using
mysqlhotcopy
is also possible
Check for corruption
mysqlcheck -uuser -ppasswd -e --auto-repair db
Tools
Next to MySQL Navigator, phpMyAdmin is a good tool. Install:
apt-get install phpmyadmin
and go to:
http://localhost/phpmyadmin
Connectting to MySQL Server from a Remote Host
Per default you can't.
In older version comment (with '#') the line
skip-networking
in
/etc/mysql/my.cnf
Restart MySQL.
For newer versions set
bind-address 0.0.0.0
in
/etc/mysql/my.cnf
Restart MySQL
/etc/init.d/mysql restart
Set up users
mysql -uroot -p mysql
and
update user set host='%' where user='username'; update db set host='%' where user='username'; exit;
Running .sql Scripts
mysql -h host -u user -p pwd < mysql_script.sql
Or from within a MySQL session
source mysql_script.sql
You can use
cat query.sql | mysql -h host -u user -p pwd db > result.txt
to execute SQL statements in query.sql and write the results to result.txt.
mysqladmin
Usage
mysqladmin -hhost -uuser -ppwd [command]
E.g.
mysqladmin -hhost -uusr -ppwd processlist
This can also be done from within a MySQL session:
SHOW PROCESSLIST;
To kill a process with a certain pid, type
kill pid
Show Sizes of All Databases
For Debian (k/ubuntu) systems:
cd /var/lib/mysql/ ls -lap | grep / | cut -f 2 -d ':' | cut -c4-200 | xargs -n 1 du -sh
General Things To Do
Create database
mysqladmin -u root -p create dbname
Rename database
As of version 5.1.7.
RENAME DATABASE old TO new;
Prior:
mysqladmin -u root -p create old mysqldump --opt -uroot -ppwd old | mysql -uroot -ppwd new
Create User
Need to be logged into MySQL.
CREATE USER username IDENTIFIED BY 'password';
Or simply:
GRANT [privileges] ON dbname.* TO user@localhost IDENTIFIED BY 'password'; flush privileges;
[privileges] can be:
- ALL PRIVILEGES
- USAGE
- SELECT, INSERT
- ...
Note that you can use wild cards for the host
user@'%'
to allow login from any host.
However, % does not stand for localhost!
Create Tabels
Select database.
CREATE TABLE name (id INT(10) UNSIGNED, date DATETIME, text VARCHAR(20), number DOUBLE);
Copy table:
CREATE TABLE name SELECT * FROM otherTableName;
Create empty table with columns from other table:
CREATE TABLE newTbl LIKE oldTbl;
Check:
DESCRIBE name;
Load Tabels
Create text file with entries seperated with tabs. Then
LOAD DATA LOCAL INFILE '/home/name.txt' INTO TABLE name LINES TERMINATED BY '\n'; LOAD DATA LOCAL INFILE '/home/name.txt' INTO TABLE name FIELDS TERMINATED BY ', ' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE '/home/name.txt' INTO TABLE name (column); LOAD DATA LOCAL INFILE '/home/name.txt' INTO TABLE name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (c1,c2, c3);
Don't forget to make sure the files can be read by MySQL.
See possible problems using \n.
Note that the LOCAL keyword allows you to connect to a remote server and still read the local files.
Copy Tabels
CREATE TABLE new_tbl SELECT * FROM orig_tbl;
Copy Tables across Servers
mysqldump -hhost -uusr -ppwd dbname table | mysql -uusr2 -ppwd2 dbname2
Copy Columns in Same Table
update table_name set new_col = old_col;
Copy Database
Do a mysqldump of the database
mysqldump -uuser -ppwd database > ~/tmp/database.sql
and recreate new database from (text) sql file
create database newDb; use newDb; source ~/tmp/database.sql;
Optimize
Check if queries are using indexes:
EXPLAIN SELECT * FROM ... WHERE ...;
To analyze UPDATE statements, rewrite them as SELECT statements.
Create indexes:
CREATE INDEX index_name ON table (column);
Export Query to File
Dump to a text file:
SELECT ... INTO OUTFILE '/dir/name';
Inverse of LOAD DATA INFILE.
Or just pipe to MySQL:
echo "SELECT * FROM name WHERE ..." | /bin/mysql -uUSERNAME -pPASSWORD DB_NAME > /tmp/foo.txt;
Note that the path for the MySQL binary can vary (check with which mysql).
Common MySQL Stuff
Login:
mysql -u user -p
From the command line, without logging into mysql:
echo "COMMAND"|mysql -uuser -ppassword -t -hhost db
Commands
SHOW databases; USE dbname; SHOW tables; DROP database dbname;
Search
Basic
SELECT * FROM table WHERE field1 = 'value1' AND field2 <= 'value2' AND filed3 != 'value3';
SELECT * FROM table WHERE ... LIMIT 0, 100;
SELECT * FROM table WHERE ... ORDER BY column-name DESC;
SELECT * FROM table WHERE field LIKE 'value-fragment%';
SELECT * FROM table WHERE field IN ('value1','value2'));
E.g.:
SELECT * FROM table WHERE time >= '2003-01-01' AND time <= '2003-08-13' LIMIT 0, 100;
Note that AND, OR, NOT are used as in set theory. E.g.
SELECT * FROM table WHERE NOT((xxx OR yyy) AND zzz);
is the complement of the set defined by the conditions 'xxx AND zzz' OR 'yyy AND zzz'.
Number of rows
SELECT COUNT(*) FROM table;
Distinct rows
SELECT DISTINCT * FROM table WHERE ...
Wildcards
Wildcards are
- '*': standing for any value
- '%': used to match occurrences of any character, usually in combination with LIKE statements; e.g.
- '%database%' matches 'relational database tables'
- '%database' matches 'relational database'
- 'database%' matches 'database tables'
- '_' single haracter match
- '[...]' where the characters denoted by the dots are matched
Calculations
Mathematical calculations can be performed in search queries, e.g.,
SELECT quantity, price, quantity*price AS total FROM table WHERE ...;
So the output is something like
+--------+------+------+ |quantity|price |total | +--------+------+------+ | 2 | 10 | 20 |
The usage of AS assigns a name (alias) to the output column.
To count the output rows:
SET @a=0; SELECT @a:=@a+1 AS number, some_columns from table WHERE ...;
Merge Queries from Multiple Tables
Idea: 3 tables, where t1 contains id_1 and name_1, t2 contains id_2 and name_2 and t3 relates id_1 to id_2 with some attribute att:
+------+--------+ | id_1 | name_1 | +------+--------+ | 55 | xyz |
+------+--------+ | id_2 | name_2 | +------+--------+ | 7 | ABC |
+------+------+------+ | att | id_1 | id_2 | +------+------+------+ | 0.5 | 55 | 7 |
To query all three tables and customize the output to
+------+--------+--------+ | att | name_1 | name_2 | +------+--------+--------+ | 0.5 | xyz | ABC |
do:
SELECT t3.att, t1.name_1, t2.name_2 FROM t1, t2, t3 WHERE t1.id_1 = t3.id_1
AND t2.id_2 = t3.id_2 ORDER BY t1.id_1 ASC;
Histogram
If your table fields are something like
+----+-------------+ | id | observation | +----+-------------+ | 1 | 33 | | 2 | 33 | | 3 | 44 |
then
SELECT COUNT(observation), observation FROM table GROUP BY observation ORDER BY observation ASC;
will give you as output
+------------------------------+-------------+ | frequency of the observation | observation | +------------------------------+-------------+ | 2 | 33 |
Subqueries
SELECT * FROM table where column IN (SELECT column FROM ...)
Delete
DELETE FROM example WHERE age='99';
Note that you can use the keyword LIMIT
DELETE FROM example WHERE age>22 LIMIT 1;
Delete from one table matching rows via second table:
delete from myTable where id in (select id from whatever_table where ...);
Insert into Table
Inserting a SQL query result into a new table:
INSERT INTO tablename( list, of, ALL, similar, columns ) ( SELECT * FROM tablename WHERE somefield = 'somevalue' ); INSERT INTO new_table SELECT * FROM old_table WHERE ...;
Don't forget to create the new table first. Note that the SQL syntax SELECT ... INTO TABLE doesn't exist for MySQL.
Inserting values into a table:
INSERT INTO table (col1, col2, ...) VALUES(1, 'two', ...), (10, 'twenty', ...), ...;
Change Values
Updating existing records:
UPDATE table_name SET field_name=expression,... [WHERE ...] UPDATE table01 SET field04=19991022, field05=062218 WHERE field01=1;
To replace some part of a text field, use
UPDATE table_name SET name = replace(name,"text to replace","with this") WHERE name LIKE "%text to replace%";
E.g., truncate the strings in a column that end in "C":
update table set col = substr(col,1,length(col)-1) where substr(col,length(col)) = 'C';
Updates, Joins, and Views
Populate column name in table sizes with values from column company_name in table firm in database otherDb matching ids:
UPDATE sizes, otherDb.firm SET name = company_name WHERE mId=firm_id;
This can be very slow, so a better solution is
UPDATE sizes a, otherDb.firm b SET a.name=b.company_name WHERE a.mId=b.firm_id;
An example with a table containing addresses and a relational table, linking address ids to payments. Update the address table with the year of the payment from the relational table.
To display the affected rows in the address table, e.g., payments done in 2007, you can use a left join
SELECT * FROM addresses a left join payments d on a.id=d.addrid WHERE d.date >= '2007-01-01' and d.date <= '2007-12-31' order by d.date asc;
Update year of payment.
UPDATE addresses a, payments d SET a.paymentYear=substr(d.date,1,4) where a.id=d.addrid and d.date >= '2007-01-01' and d.date <= '2007-12-31';
As a different solution, I used VIEWS. Create a view with the required rows
create view seven as SELECT * from payments WHERE date >= '2007-01-01' and d.date <= '2007-12-31' order by date asc;
Then update, using the view and the ids, taking only the year from the date field
UPDATE addresses a, seven d SET a.paymentYear=substr(d.date,1,4) where a.id=d.addrid;
Compare Tables
The command
SELECT t1.c1, t1.c2 - t2.c2 FROM t1, t2 WHERE t1.c1=t2.c1 AND t1.c2 - t2.c2 != '0';
will yield an Empty set if the two tables are something like
t1: +----+----+ | c1 | c2 | +----+----+ | 1 | 11 | | 2 | 22 |
and
t2: +----+----+ | c1 | c2 | +----+----+ | 1 | 11 | | 2 | 22 |
respectively, or
+----+---------------+ | c1 | t1.c2 - t2.c2 | +----+---------------+ | no | diff |
if they differ.
ALTER Command
Rename table
ALTER TABLE tname RENAME newtname;
With
ALTER TABLE tblname ...
one can modify and add columns. E.g.
ALTER TABLE tblname ADD newcol newcoltype AFTER somecol; ALTER TABLE tblname ADD newcol newcoltype FIRST; ALTER TABLE tblname CHANGE oldname newname type;
Change column type
ALTER TABLE name CHANGE colName colName newType;
Delete column:
ALTER TABLE name DROP COLUMN colName;
Joins
See also the example Joins, Unions and Subqueries.
Inner Joins
As an example, we have table one and table two, each with an id and name column.
SELECT * FROM one, two WHERE one.name=two.name;
is equivalent to
SELECT * FROM one INNER JOIN two WHERE one.name=two.name;
Convenient
SELECT o.name, t.name FROM one AS o, two AS t WHERE o.name=t.name;
Self reference (this example makes no sens, but illustrates the syntax)
SELECT o1.id, o1.name FROM one AS o1, one AS o2 WHERE o1.name=o2.name;
Such queries can also always be performed using subqueries. However, performance is often better for the joins.
Example: one table of firms with ids and names and a relational table linking firms owning firms. Show id and names of owner and owned firms:
select f.firm_id, s.firm_id, f.company_name, s.company_name from firm_firm r inner join firm f on f.firm_id=r.owned_id inner join firm s on s.firm_id=r.owner_id;
Outer Joins
Again, as an example, we have table one and table two, each with an id and name column.
Note that if the two tables contain 5 rows each, then the join
SELECT * FROM one, two;
will yield 25 (=5x5) entries and
SELECT * FROM one, two WHERE one.name=two.name;
returns x overlaps in name and
SELECT * FROM one, two WHERE one.name!=two.name;
all the other (25-x) combinations.
If you want to display all elements in one that are not in two
SELECT one.name FROM one LEFT OUTER JOIN two ON one.name = two.name GROUP BY one.name HAVING COUNT(two.name) =0; SELECT o.name FROM one o LEFT JOIN two t ON o.name = t.name WHERE t.name IS NULL;
and vice versa
SELECT two.name FROM one RIGHT OUTER JOIN two ON one.name = two.name GROUP BY two.name HAVING COUNT(one.name) =0; SELECT t.name FROM one o RIGHT JOIN two t ON o.name = t.name WHERE o.name IS NULL;
The overlapping elements
SELECT one.name FROM one LEFT OUTER JOIN two ON one.name = two.name GROUP BY one.name HAVING COUNT(two.name) =1; SELECT o.name FROM one o LEFT JOIN two t ON o.name = t.name WHERE t.name IS NOT NULL; SELECT two.name FROM one RIGHT OUTER JOIN two ON one.name = two.name GROUP BY two.name HAVING COUNT(one.name) =1; SELECT t.name FROM one o RIGHT JOIN two t ON o.name = t.name WHERE o.name IS NOT NULL;
and the rest
SELECT two.name FROM one RIGHT OUTER JOIN two ON one.name = two.name GROUP BY two.name HAVING COUNT(one.name) =0 UNION SELECT one.name FROM one LEFT OUTER JOIN two ON one.name = two.name GROUP BY one.name HAVING COUNT(two.name) =0;
Three joins:
SELECT o.name FROM one o LEFT JOIN two t USING (name) LEFT JOIN three th ON t.name = th.name WHERE th.name IS NOT NULL;
Comparing the Two
You have three tables, two containing detail information called firm and subsidiary, and a relational table linking the two via firm_id and subsidiary_id called firmsubsidiary.
To display information from firm and subsidary (e.g., names) using the relational information given in firmsubsidiary either use inner joins:
SELECT fs.firm_id, f.company_name, fs.subsidiary_id, s.subsidiary_name, s.subsidiary_country FROM firmsubsidiary fs, firm f, subsidiary s WHERE fs.firm_id=f.firm_id AND fs.subsidiary_id=s.subsidiary_id;
or outer joins:
SELECT fs.firm_id, f.company_name, fs.subsidiary_id, s.subsidiary_name, s.subsidiary_country FROM firmsubsidiary fs left join firm f using (firm_id) left join subsidiary s using (subsidiary_id);
Views
Views are virtual tables containing query statements. Handling identical to tables (drop, rename,...). Cerate view:
CREATE VIEW name AS SELECT ...;
Drop:
DROP VIEW name;
Auto Increment
CREATE TABLE name (name type AUTO_INCREMENT);
To reassign auto incrementing, i.e., redo auto_incrementing starting from 1, drop column
ALTER TABLE name DROP COLUMN col;
and recreate, e.g.,
ALTER TABLE name ADD col int(10) PRIMARY KEY AUTO_INCREMENT FIRST;
If you just want to reset the counter:
ALTER TABLE name AUTO_INCREMENT=1;
Info
SHOW TABLE STATUS like '%name';
SHOW INDEX FROM name;
DESC name;
More Examples
Deleting Multiple Duplicate Rows
This didn't really work.
Extract and Count Duplicates
CREATE TABLE tmp AS (SELECT * FROM ( SELECT *, count( * ) AS c FROM mytable GROUP BY id1, id2 ORDER BY count( * ) DESC ) AS whatever WHERE c > 1);
alter table tmp add primary key (id1, id2);
Delete Duplicates from Original
Match by ids:
delete from mytable where id1 in (select id1 from tmp) and id2 in (select id2 from tmp);
Copy Distinct Rows Back
ALTER TABLE tmp DROP COLUMN c;
INSERT INTO mytable SELECT * FROM tmp;
alter table mytable add primary key (id1, id2);
drop table tmp;
Distinct Fields
Two tables called firm and firm_year, where firm contains the data fields and firm_year the relational data, e.g., firm_year.firm_id gets mapped via firm.firm_id to firm_firm_name.
select distinct y.firm_id, f.company_name, y.number_of_employees_, y.year from firm_year y, firm f where number_of_employees_ >= 2000 and y.firm_id = f.firm_id order by y.firm_id, year asc limit 20;
gives
+---------+---------------------------+----------------------+------+ | firm_id | company_name | number_of_employees_ | year | +---------+---------------------------+----------------------+------+ | 1 | NESTLE SA | 220172 | 1995 | | 1 | NESTLE SA | 221144 | 1996 | | 1 | NESTLE SA | 225808 | 1997 |
To have each firm displayed only once, i.e., distinct field firm_id, use
select distinct y.firm_id, f.company_name, y.number_of_employees_, y.year, count(y.firm_id) from firm_year y, firm f where number_of_employees_ >= 2000 and y.firm_id = f.firm_id group by y.firm_id having count(y.firm_id)>=1 order by y.firm_id, year asc limit 20;
i.e.,
+---------+-------------------------------------+----------------------+------+------------------+ | firm_id | company_name | number_of_employees_ | year | count(y.firm_id) | +---------+-------------------------------------+----------------------+------+------------------+ | 1 | NESTLE SA | 220172 | 1995 | 9 | | 2 | ROCHE HOLDING AG | 48972 | 1996 | 8 |
Problem: how to order that the displayed years are the more recent ones...
Perhaps this could help (http://dev.mysql.com/doc/refman/5.0/en/select.html):
If you want to use ORDER BY before GROUP BY, the only way I've found to achieve it is with a subquery. For example, if you want to get a list of users from a table UserActions sorted according to the most recent action (based on a field called Time) the query would be: SELECT * FROM (SELECT * FROM UserActions ORDER BY Time DESC) AS Actions GROUP BY UserID ORDER BY Time DESC;
Subqueries and Joins
You have three tables, two containing detail information called firm and subsidiary, and a relational table linking the two via firm_id and subsidiary_id called firmsubsidiary.
Count how many subsidiaries a firm has grouped by country.
Link the Detail Information
SELECT fs.firm_id, f.company_name, fs.subsidiary_id, s.subsidiary_name, s.subsidiary_country FROM firmsubsidiary fs, firm f, subsidiary s WHERE fs.firm_id=f.firm_id AND fs.subsidiary_id=s.subsidiary_id;
or
SELECT fs.firm_id, f.company_name, fs.subsidiary_id, s.subsidiary_name, s.subsidiary_country FROM firmsubsidiary fs left join firm f using (firm_id) left join subsidiary s using (subsidiary_id);
yields
+---------+--------------+---------------+------------------------------------------+--------------------+ | firm_id | company_name | subsidiary_id | subsidiary_name | subsidiary_country | +---------+--------------+---------------+------------------------------------------+--------------------+
Save this output as a VIEW, i.e., use it like a table:
CREATE VIEW supertmp AS SELECT fs.firm_id, f.company_name, fs.subsidiary_id, s.subsidiary_name, s.subsidiary_country FROM firmsubsidiary fs, firm f, subsidiary s WHERE fs.firm_id=f.firm_id AND fs.subsidiary_id=s.subsidiary_id;
or
CREATE VIEW supertmp AS SELECT fs.firm_id, f.company_name, fs.subsidiary_id, s.subsidiary_name, s.subsidiary_country FROM firmsubsidiary fs left join firm f using (firm_id) left join subsidiary s using (subsidiary_id);
Get Country List
SELECT DISTINCT subsidiary_country from supertmp;
Count Subsidiaries
Total number:
SELECT COUNT(*) FROM supertmp WHERE firm_id=1;
An example where the country is Switzerland:
SELECT COUNT(*) FROM supertmp WHERE firm_id=1 AND subsidiary_country='CH';
Combine the Two Queries
SELECT COUNT(*), company_name, subsidiary_country FROM supertmp WHERE firm_id=1 AND subsidiary_country IN (SELECT DISTINCT subsidiary_country from supertmp) GROUP BY subsidiary_country ORDER BY COUNT(*) DESC;
Time Series Increments
You have a time series table with an id, date and value. You want to compute the increments from on tick to the next:
set @m:= [first mid value]; select quoteId, date, mid, mid-@m as delta, @m:=mid as tmp from timeSeries order by quoteId asc;
which yields
+---------+---------------------+------------------+----------------------+-----------------+ | quoteId | date | mid | delta | tmp | +---------+---------------------+------------------+----------------------+-----------------+ | 1 | 2004-08-01 22:00:02 | 1.33672283859437 | -3.7747582837255e-15 | 1.3367228385944 | | 2 | 2004-08-01 22:00:15 | 1.33673934253091 | 1.6503936542733e-05 | 1.3367393425309 |
If you want to add this information to the table, then make sure your column quoteId is the primary key, and
ALTER TABLE timeSeries ADD delta double AFTER mid; ALTER TABLE timeSeries ADD tmp double AFTER delta;
Then
replace into timeSeries (quoteId, date, mid, delta, tmp) SELECT quoteId, date, mid, mid-@m, @m:=mid FROM timeSeries order by quoteId asc;
Finally,
alter table timeSeries drop column tmp;
Joins, Unions and Subqueries
You have a relational table firm_firm with ids of owner and owned firms from the table firm.
A list of distinct owner ids where owner and owned firms are located in Switzerland:
SELECT distinct owner_id FROM firm_firm r INNER JOIN firm p on r.owner_id = p.firm_id INNER JOIN firm p2 on r.owned_id = p2.firm_id where p.country_iso_code ="CH" and p2.country_iso_code ="CH";
Correspondingly for owned ids:
SELECT distinct owned_id FROM firm_firm r INNER JOIN firm p on r.owner_id = p.firm_id INNER JOIN firm p2 on r.owned_id = p2.firm_id where p.country_iso_code ="CH" and p2.country_iso_code ="CH";
To see how many unique firms are involved, you combine the above queries with the UNION keyword:
SELECT distinct r.owned_id FROM firm_firm r INNER JOIN firm p on r.owner_id = p.firm_id INNER JOIN firm p2 on r.owned_id = p2.firm_id where p.country_iso_code ="CH" and p2.country_iso_code ="CH" union distinct SELECT distinct owner_id FROM firm_firm r INNER JOIN firm p on r.owner_id = p.firm_id INNER JOIN firm p2 on r.owned_id = p2.firm_id where p.country_iso_code ="CH" and p2.country_iso_code ="CH";
To know which firms are owners and owned, you need a subquery like
SELECT * FROM (SELECT ...) AS one, (SELECT ...) AS two WHERE one.xyz = two.abc;
i.e.
select * from (SELECT distinct r.owned_id FROM firm_firm r INNER JOIN firm p on r.owner_id = p.firm_id INNER JOIN firm p2 on r.owned_id = p2.firm_id where p.country_iso_code ="CH" and p2.country_iso_code ="CH") as owned, (SELECT distinct owner_id FROM firm_firm r INNER JOIN firm p on r.owner_id = p.firm_id INNER JOIN firm p2 on r.owned_id = p2.firm_id where p.country_iso_code ="CH" and p2.country_iso_code ="CH") as owner where owned.owned_id=owner.owner_id;
Problems
New Lines using \n
On a Linux box \n denotes a new line, i.e, endline. E.g. Load Tabels. However, Windows uses
\r
!
This can be a source of problems...
Formatting
On kubuntu (dapper) with mysql 5.0.21 a utf-8 a mysqldump-file didn't restore the correct formatting.
Solution:
Recreate dumpfile:
mysqldump -uroot -p --default-character-set=latin database > latin_name.sql
Convert:
iconv -f ISO-8859-1 -t UTF-8 latin_name.sql -o iso_name.sql
Reload:
mysql -uroot -p database < iso_name.sql
Wrong debian-sys-maint Password
If you by accident (e.g. reloading a dumpfile containing the mysql system databases onto a different server,...) delete the password for the debian-sys-maint user you get error messages when restarting mysql (/etc/init.d/mysql restart) and can fix it by
- getting password from /etc/mysql/debian.cnf (it is plain text and not encrypted!)
- in mysql generate encription:
SELECT PASSWORD('pwd');
- change password in table:
UPDATE user SET Password='encripted-pwd' WHERE User='debian-sys-maint';
Binary Logs
Running out of disk space because
/var/lib/mysql/
is full of
mysql-bin.000*
log files?
If you don't use replication, you can get rid of the files by typing
RESET MASTER
in the mysql console.
and stop them being written, by commenting (put a '#' in front of it) in
/etc/my.cnf
the line
log-bin=mysql-bin
Btw, you can read them with the command
mysqlbinlog
Speed
If speed's an issue, look here:
Additional Stuff
Matlab
See Matlab and MySQL.
