Administrating

cloning a utf-8 (hebrew for example) sql

do a mysqldump or whatever;

have a something.sql file

run:

mysql --default-character-set=utf8 -u<username> -p<password> databasename < something.sql

utf-8 stuff in my.cnf

[mysqld]                                                                                                             
default-character-set=utf8                                                                                           
character-set-server=utf8                                                                                            
default-collation=utf8_unicode_ci                                                                                    
[client]                                                                                                             
default-character-set = utf8

Priviliges table - upgrading to 5.0

From http://www.john-hunt.com/linux/2006/05/12/mysql-upgrade-breaking-things/

mysqld_safe –skip-grant-tables –user=root &
mysql_fix_privilege_tables
pkill mysqld
/etc/init.d/mysqld restart

Creating a user and granting it privileges on an existing database

CREATE USER username IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';
update mysql.user set password=password('password') where host='localhost' and user='username';

Creating a user

CREATE USER username IDENTIFIED BY 'password';

Change password

SET PASSWORD FOR 'USERNAME'@'localhost or maybe %' = PASSWORD('XXX');

Show existing users

use mysql
select User,Host,Password from user;

Creating Databases and Using

Creating a mail database and folder

as root (or anyone with create privileges)

CREATE DATABASE alonmail CHARACTER SET utf8;
GRANT ALL PRIVILEGES ON alonmail.* TO 'alonl'@'localhost';
FLUSH PRIVILEGES;

as alonl

USE alonmail
CREATE TABLE incoming (subject VARCHAR(50), sender VARCHAR(50), sent DATE, logged DATE);
describe incoming;
show create table incoming;

after adding an entry the table string sizes are too small

alter table incoming change subject subject varchar(250);

then clear it

delete from incoming;

then add something

insert into incoming values ('electronics cable, made in canada, JTAG to parallel','Rena Electronics inc. 8B-250 Greenbank Road, suite 122, Nepean, Ontario, K2H 1E9, Canada',2006-05-29,2006-06-04)

Change the table:

alter table incoming add owner varchar(250) first;
alter table incoming drop owner;

Changing default database charset

You may want all data to be utf8. When you create a table, it takes the default charset from the database. To change a database's default charset:

alter database thedatabase default charset utf8

Snippets

Alter a default value for a column

alter table table_name alter column column_name set default some_literal;

Boolean

1 is true, 0 is false.

Resetting password

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

Loading a schema

If you have a schema file that should be sourced (like installing tiny tiny rss), you need to prefix it with the table and the login. For Mysql it goes:

mysql -uusername -ppassword -Ddb_to_use < schema_file_which_expects_db_to_already_be_chosen