Setting up the Mysql Master Server

# mysql -u root -p

mysql > GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO replicant@'192.168.1.139' IDENTIFIED BY 'mypass';

mysql > FLUSH PRIVILEGES;

mysql > quit

#mysqldump --user=root --password= --extended-insert --all-databases --master-data > /tmp/backup.sql

Import sql in Salve computer

mysql --user=root --password=my_pwd < /tmp/backup.sql

binlogging on server not active

open /etc/my.cnf in MASTER computer
add the following line


# The MySQL server

[mysqld]

log-bin=/var/log/mysql/bin.log
( please confirm the actually folder existing and right for mysql )

server-id = 1


save it and restart Mysql ( # serivce mysqld restart )

redirect from http://www.hmes.kh.edu.tw/~jona/redhat/mysqlphp/mysqlsyntax.htm

MySQL的重要語法

一、帳號與權限

設定 root 和其他 user 的密碼

• mysqladmin -u root password '新密碼'
• mysqladmin -u root -p
• Enter password: 　　　此時再輸入密碼(建議採用)
• use mysql;
  mysql> UPDATE user SET password=password('新密碼') where user='root';　
  　　只改 root 的密碼，如果沒有用 where ，則表示改全部 user 的密碼
• mysql> FLUSH PRIVILEGES;　在 mysql 資料庫內，一定要用 flush 更新記憶體上的資料

刪除空帳號

• mysql> DELETE FROM user WHERE User = '';
• mysql> FLUSH PRIVILEGES;
  　

建立新帳號

• mysql> GRANT 權限 ON 資料庫或資料表 TO 使用者 IDENTIFIED BY '密碼';
  　　權限
  　　　
  　　資料庫或資料表
  　　　*.*　所有資料庫裡的所有資料表
  　　　*　預設資料庫裡的所有資料表
  　　　資料庫.*　某一資料庫裡的所有資料表　　　
  　　　資料庫.資料表　某一資料庫裡的特定資料表
  　　　資料表　　預設資料庫裡的某一資料表

設定/修改權限 　

• 用 root 登入 MySQL
  　 mysqladmin -u root -p
  　Enter password: 　　
• mysql> GRANT all ON db35.* TO s35@'localhost' IDENTIFIED BY 's35';
  　　　把 db35 這個資料庫(含其下的所有資料表)，授權給 s35，從 localhost 上來，密碼為s35
  　
• mysql> GRANT all ON *.*　 把所有資料庫及資料表授權給別人，太危險了！
  　
• mysql> GRANT all??? ON www.* TO '*'@'*' IDENTIFIED BY '';
  　　　 把 www 這個資料庫(含其下的所有資料表)，授權給 任何機器任何人，無密碼（通常給不特定人士使用）
  　
• mysql> FLUSH PRIVILEGES; （最後一定要強迫更新權限）

　

二、資料庫/資料表/欄位的操作

建立資料庫　CREATE DATABASE 資料庫名;
語法：CREATE DATABASE db_name

使用資料庫　USE 資料庫名;
語法：USE db_name

刪除資料庫　DROP DATABASE 資料庫名;
語法：DROP DATABASE [IF EXISTS] db_name
　
建立資料表
　CREATE TABLE 資料表名 (欄位1 資料型態, 欄位2 資料型態, ......);
語法：
CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [select_statement]

例：
craete database basic;
use basic;
create table basic(
　　no char(4)
　　name char(10)
　　id char(10));

create_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] KEY(index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
or CHECK (expr)

資料結構(type):

資料型態	說明
TINYINT	有符號的範圍是-128到127， 無符號的範圍是0到255。
SMALLINT	有符號的範圍是-32768到32767， 無符號的範圍是0到65535。
MEDIUMINT	有符號的範圍是-8388608到8388607， 無符號的範圍是0到16777215。
INT	有符號的範圍是-2147483648到2147483647， 無符號的範圍是0到4294967295。
INTEGER	INT的同義詞。
BIGINT	有符號的範圍是-9223372036854775808到 9223372036854775807，無符號的範圍是0到18446744073709551615。
FLOAT	單精密浮點數字。不能無符號。允許的值是-3.402823466E+38到- 1.175494351E-38，0 和1.175494351E-38到3.402823466E+38。
DOUBLE	雙精密)浮點數字。不能無符號。允許的值是- 1.7976931348623157E+308到-2.2250738585072014E-308、 0和2.2250738585072014E-308到1.7976931348623157E+308。
DOUBLE PRECISION	DOUBLE的同義詞。
REAL	DOUBLE的同義詞。
DECIMAL	DECIMAL值的最大範圍與DOUBLE相 同。
NUMERIC	DECIMAL的同義詞。
DATE	日期。支援的範圍是'1000-01-01'到'9999-12-31'。
DATETIME	日期和時間組合。支援的範圍是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'
TIMESTAMP	時間戳記。範圍是'1970-01-01 00:00:00'到2037年的某時。
TIME	一個時間。範圍是'-838:59:59'到'838:59:59'。
YEAR	2或4位數字格式的年(內定是4位)。允許的值是1901到2155。
CHAR	固定長度，1 ～ 255個字元。
VARCHAR	可變長度，1 ～ 255個字元。
TINYBLOB
TINYTEXT	最大長度為255(2^8-1)個字符。
MEDIUMBLOB
MEDIUMTEXT	最大長度為16777215(2^24-1)個字符。
LONGBLOB
LONGTEXT	最大長度為4294967295(2^32-1)個字符。
ENUM	一個ENUM最多能有65535不同的值。
SET	一個SET最多能有64個成員。

index_col_name:
col_name [(length)]

reference_definition:
REFERENCES tbl_name [(index_col_name,...)]
[MATCH FULL | MATCH PARTIAL]
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
type = [ISAM | MYISAM | HEAP]
or max_rows = #
or min_rows = #
or avg_row_length = #
or comment = "string"
or auto_increment = #
select_statement:
[ | IGNORE | REPLACE] SELECT ... (Some legal select statement)

刪除資料表　DROP TABLE 資料表名;
語法：DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]

改變資料表結構(新增/刪除欄位、建立/取消索引、改變欄位資料型態、欄位重新命 名)
語法：
ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
or MODIFY [COLUMN] create_definition
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX key_name
or RENAME [AS] new_tbl_name
or table_option
範例：
　欄位重新命名
　mysql> ALTER TABLE t1 CHANGE a b INTEGER;
　　將資料表 t1 欄位 a 改名為 b (其資料型態是 integer)
　改變欄位資料型態
　mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
　mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
　　將資料表 t1 欄位 b 的資料型態改為 bigint not null　

　mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));　
　mysql> ALTER TABLE t1 RENAME t2;
　　將資料表 t1 改名為 t2
　mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
　　將資料表 t2 欄位 a 資料型態由 integer 改為 tinyint not null ，欄位 b 改名為 c 資料型態改為 char(20)
　mysql> ALTER TABLE t2 ADD d TIMESTAMP;
　　在資料表 t2 增加新欄位 d 資料型態是 timestamp
　mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
　　在資料表 t2 ，對 d 欄位做索引，並以欄位 a 作為主索引鍵
　mysql> ALTER TABLE t2 DROP COLUMN c;
　　刪除欄位 c
　mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);
　　新增欄位 c，並做索引(做索引的欄位必須為 not null )

資料表最佳化　OPTIMIZE TABLE 資料表名
語法：OPTIMIZE TABLE tbl_name
　欄位長度有變動、刪除大量資料，都應進行資料表最佳化

三、紀錄的操作

插入一筆或多筆紀錄　INSERT INTO 資料表(欄位1,欄位2,......) VALUES(值1,值2,......), (值1,值2,......), ........
　　(MySQL 3.22.5 以後可插入多筆記錄)
語法：
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...),(...),...
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name
SET col_name=expression, col_name=expression, ...
範例：
　mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
　　不可寫成
　mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);
　　因為：欄位 col1 的值先填入後，才可以計算欄位 col2

從檔案讀入資料
語法：
　LOAD DATA [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t'] 　　　每一欄位以某字元分開(內定是 tab)
[OPTIONALLY] ENCLOSED BY "] 　　每一欄位以某字元括住(內定是不使用括號)
[ESCAPED BY '\\' ]] 　　　　
[LINES TERMINATED BY '\n']　　設定換行的字元(內 定是 \n)
[IGNORE number LINES] 　　　忽略最前面幾行(最前面幾筆記錄不抄進來)
[(col_name,...)]
範例：
　mysql> USE db1;
　mysql> LOAD DATA INFILE "./data.txt" INTO TABLE db2.my_table;
　　從目前 MySQL 目錄讀入 data.txt
　mysql> LOAD DATA INFILE "./88.txt" INTO TABLE TEACHER FIELDS TERMINATED BY ' ' ;
　　從目前 MySQL 目錄(我的在 /var/lib/mysql )讀入 data.txt ，每一欄位以 空白 分開
　mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
　　只將 persondata.txt 裡某些欄位的資料抓過來　

刪除紀錄　DELETE [LOW-PRIORITY] FROM 資料表名 WHERE 條件 [LIMIT rows]
語法：
　DELETE [LOW_PRIORITY] FROM tbl_name
[WHERE where_definition] [LIMIT rows]
　　LOW-PRIORITY　是等到沒有用戶端使用時再刪
　　LIMIT rows　限制刪除紀錄的筆數
範例：
　mysql> DELETE FROM 資料表名;
　　刪除所有紀錄
　mysql> DELETE FROM 資料表名 WHERE 1>0;
　　刪除所有紀錄，但速度較慢，方便在螢幕上看

更新一筆紀錄 (語法與 INSERT 相同)
　REPLACE INTO 資料表(欄位1,欄位2,......) VALUES(值1,值2,......)
語法：
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...)
or REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name=expression, col_name=expression,...

更新多筆紀錄
語法：
UPDATE [LOW_PRIORITY] tbl_name SET col_name1=expr1,col_name2=expr2,... [WHERE where_definition]
　　如果沒有設定 WHERE 條件，則整個資料表相關的欄位都更新
範例：
　mysql> UPDATE persondata SET age=age+1;
　　將資料表 persondata 中，所有 age 欄位都加 1
　mysql> UPDATE persondata SET age=age*2, age=age+1;
　　將資料表 persondata 中，所有 age 欄位都*2，再加 1

四、資料的輸出

SELECT

語法：
　SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [DISTINCT | ALL]
select_expression,...
[INTO OUTFILE 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY col_name,...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name] ]

範例：

排序輸出
select * from 資料表名 order by 欄位名1,欄位名2,欄位名3......　

反向排序輸出
select * from 資料表名 order by 欄位名1,欄位名2,欄位名3...... desc

　

mysql> select concat(last_name,', ',first_name) AS full_name

　　　　　　from mytable ORDER BY full_name;



　mysql> select t1.name, t2.salary from employee AS t1, info AS t2

where t1.name = t2.name;

　　顯示資料庫 employee(別名 t1) 裡，資料表 t1 的欄位 name 和 資料表 t2 的欄位 salary 當.....

　mysql> select t1.name, t2.salary from employee t1, info t2 where t1.name = t2.name;



　mysql> select college, region, seed from tournament

ORDER BY region, seed;

　mysql> select college, region AS r, seed AS s from tournament

ORDER BY r, s;

　mysql> select college, region, seed from tournament

ORDER BY 2, 3;



　mysql> select col_name from tbl_name HAVING col_name > 0;



　mysql> select col_name from tbl_name WHERE col_name > 0;



　mysql> select user,max(salary) from users

group by user HAVING max(salary)>10;



mysql> select user,max(salary) AS sum from users

group by user HAVING sum>10;



　mysql> select * from table LIMIT 5,10; # Retrieve rows 6-15



　mysql> select * from table LIMIT 5; # Retrieve first 5 rows

• 　

在命令列下進行批次處理：
shell> mysql -h host -u user -p <>

Type	Bytes	From	To
TINYINT	1	-128	127
SMALLINT	2	-32768	32767
MEDIUMINT	3	-8388608	8388607
INT	4	-2147483648	2147483647
BIGINT	8	-9223372036854775808	9223372036854775807

Column type	``Zero'' value
DATETIME	'0000-00-00 00:00:00'
DATE	'0000-00-00'
TIMESTAMP	00000000000000 (length depends on display size)
TIME	'00:00:00'
YEAR	0000

Column type	Display format
TIMESTAMP(14)	YYYYMMDDHHMMSS
TIMESTAMP(12)	YYMMDDHHMMSS
TIMESTAMP(10)	YYMMDDHHMM
TIMESTAMP(8)	YYYYMMDD
TIMESTAMP(6)	YYMMDD
TIMESTAMP(4)	YYMM
TIMESTAMP(2)	YY

Type	Max.size	Bytes
TINYTEXT or TINYBLOB	2^8-1	255
TEXT or BLOB	2^16-1 (64K-1)	65535
MEDIUMTEXT or MEDIUMBLOB	2^24-1 (16M-1)	16777215
LONGBLOB	2^32-1 (4G-1)	4294967295

Value	CHAR(4)	Storage required	VARCHAR(4)	Storage required
''	' '	4 bytes	''	1 byte
'ab'	'ab '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 bytes

Value	Index
NULL	NULL
""	0
"one"	1
"two"	2
"three"	3

Other vendor type	MySQL type
BINARY(NUM)	CHAR(NUM) BINARY
CHAR VARYING(NUM)	VARCHAR(NUM)
FLOAT4	FLOAT
FLOAT8	DOUBLE
INT1	TINYINT
INT2	SMALLINT
INT3	MEDIUMINT
INT4	INT
INT8	BIGINT
LONG VARBINARY	MEDIUMBLOB
LONG VARCHAR	MEDIUMTEXT
MIDDLEINT	MEDIUMINT
VARBINARY(NUM)	VARCHAR(NUM) BINARY

Column type	Storage required
TINYINT	1 byte
SMALLINT	2 bytes
MEDIUMINT	3 bytes
INT	4 bytes
INTEGER	4 bytes
BIGINT	8 bytes
FLOAT(X)	4 if X <= 24 or 8 if 25 <= X <= 53
FLOAT	4 bytes
DOUBLE	8 bytes
DOUBLE PRECISION	8 bytes
REAL	8 bytes
DECIMAL(M,D)	M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M <>)
NUMERIC(M,D)	M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M <>)

Column type	Storage required
DATE	3 bytes
DATETIME	8 bytes
TIMESTAMP	4 bytes
TIME	3 bytes
YEAR	1 byte

Column type	Storage required
CHAR(M)	M bytes, 1 <= M <= 255
VARCHAR(M)	L+1 bytes, where L <= M and 1 <= M <= 255
TINYBLOB, TINYTEXT	L+1 bytes, where L <>
BLOB, TEXT	L+2 bytes, where L <>
MEDIUMBLOB, MEDIUMTEXT	L+3 bytes, where L <>
LONGBLOB, LONGTEXT	L+4 bytes, where L <>
ENUM('value1','value2',...)	1 or 2 bytes, depending on the number of enumeration values (65535 values maximum)
SET('value1','value2',...)	1, 2, 3, 4 or 8 bytes, depending on the number of set members (64 members maximum)

Securing the Initial MySQL Accounts

http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html

Part of the MySQL installation process is to set up the mysql database that contains the grant tables:

• Windows distributions contain preinitialized grant tables that are installed automatically.

• On Unix, the grant tables are populated by the mysql_install_db program. Some installation methods run this program for you. Others require that you execute it manually. For details, see Section 2.17.2, 「Unix Post-Installation Procedures」.

The grant tables define the initial MySQL user accounts and their access privileges. These accounts are set up as follows:

• Accounts with the username root are created. These are superuser accounts that can do anything. The initial root account passwords are empty, so anyone can connect to the MySQL server as root — without a password — and be granted all privileges.

  • On Windows, one root account is created; this account allows connecting from the local host only. The Windows installer will optionally create an account allowing for connections from any host only if the user selects the Enable root access from remote machines option during installation.

  • On Unix, both root accounts are for connections from the local host. Connections must be made from the local host by specifying a hostname of localhost for one of the accounts, or the actual hostname or IP number for the other.

• Two anonymous-user accounts are created, each with an empty username. The anonymous accounts have no password, so anyone can use them to connect to the MySQL server.

  • On Windows, one anonymous account is for connections from the local host. It has no global privileges. (Before MySQL 5.1.16, it has all global privileges, just like the root accounts.) The other is for connections from any host and has all privileges for the test database and for other databases with names that start with test.

  • On Unix, both anonymous accounts are for connections from the local host. Connections must be made from the local host by specifying a hostname of localhost for one of the accounts, or the actual hostname or IP number for the other. These accounts have all privileges for the test database and for other databases with names that start with test_.

As noted, none of the initial accounts have passwords. This means that your MySQL installation is unprotected until you do something about it:

• If you want to prevent clients from connecting as anonymous users without a password, you should either assign a password to each anonymous account or else remove the accounts.

• You should assign a password to each MySQL root account.

The following instructions describe how to set up passwords for the initial MySQL accounts, first for the anonymous accounts and then for the root accounts. Replace 「newpwd」 in the examples with the actual password that you want to use. The instructions also cover how to remove the anonymous accounts, should you prefer not to allow anonymous access at all.

You might want to defer setting the passwords until later, so that you don't need to specify them while you perform additional setup or testing. However, be sure to set them before using your installation for production purposes.

Anonymous Account Password Assignment

To assign passwords to the anonymous accounts, connect to the server as root and then use either SET PASSWORD or UPDATE. In either case, be sure to encrypt the password using the PASSWORD() function.

To use SET PASSWORD on Windows, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR ''@'%' = PASSWORD('newpwd');

To use SET PASSWORD on Unix, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR ''@'host_name' = PASSWORD('newpwd');

In the second SET PASSWORD statement, replace host_name with the name of the server host. This is the name that is specified in the Host column of the non-localhost record for root in the user table. If you don't know what hostname this is, issue the following statement before using SET PASSWORD:

mysql> SELECT Host, User FROM mysql.user;

Look for the record that has root in the User column and something other than localhost in the Host column. Then use that Host value in the second SET PASSWORD statement.

Anonymous Account Removal

If you prefer to remove the anonymous accounts instead, do so as follows:

shell> mysql -u root
mysql> DROP USER '';

The DROP statement applies both to Windows and to Unix. On Windows, if you want to remove only the anonymous account that has the same privileges as root, do this instead:

shell> mysql -u root
mysql> DROP USER ''@'localhost';

That account allows anonymous access but has full privileges, so removing it improves security.

root Account Password Assignment

You can assign passwords to the root accounts in several ways. The following discussion demonstrates three methods:

• Use the SET PASSWORD statement

• Use the mysqladmin command-line client program

• Use the UPDATE statement

To assign passwords using SET PASSWORD, connect to the server as root and issue SET PASSWORD statements. Be sure to encrypt the password using the PASSWORD() function.

For Windows, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('newpwd');

For Unix, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');

In the second SET PASSWORD statement, replace host_name with the name of the server host. This is the same hostname that you used when you assigned the anonymous account passwords.

If the user table contains an account with User and Host values of 'root' and '127.0.0.1', use an additional SET PASSWORD statement to set that account's password:

mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');

To assign passwords to the root accounts using mysqladmin, execute the following commands:

shell> mysqladmin -u root password "newpwd"
shell> mysqladmin -u root -h host_name password "newpwd"

These commands apply both to Windows and to Unix. In the second command, replace host_name with the name of the server host. The double quotes around the password are not always necessary, but you should use them if the password contains spaces or other characters that are special to your command interpreter.

The mysqladmin method of setting the root account passwords does not set the password for the 'root'@'127.0.0.1' account. To do so, use SET PASSWORD as shown earlier.

You can also use UPDATE to modify the user table directly. The following UPDATE statement assigns a password to all root accounts:

shell> mysql -u root
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd')
 ->     WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

The UPDATE statement applies both to Windows and to Unix.

After the passwords have been set, you must supply the appropriate password whenever you connect to the server. For example, if you want to use mysqladmin to shut down the server, you can do so using this command:

shell> mysqladmin -u root -p shutdown
Enter password: (enter root password here)

Installing MySQL from tar.gz Packages on Other Unix-Like Systems

http://dev.mysql.com/doc/refman/5.0/en/installing-binary.html

This section covers the installation of MySQL binary distributions that are provided for various platforms in the form of compressed tar files (files with a .tar.gz extension). See Section 2.4.3.4, 「MySQL Binaries Compiled by MySQL AB」, for a detailed list.

To obtain MySQL, see Section 2.5, 「How to Get MySQL」.

MySQL tar file binary distributions have names of the form mysql-VERSION-OS.tar.gz, where VERSION is a number (for example, 5.0.72), and OS indicates the type of operating system for which the distribution is intended (for example, pc-linux-i686).

In addition to these generic packages, we also offer binaries in platform-specific package formats for selected platforms. See Section 2.8, 「Standard MySQL Installation Using a Binary Distribution」, for more information on how to install these.

You need the following tools to install a MySQL tar file binary distribution:

• GNU gunzip to uncompress the distribution.

• A reasonable tar to unpack the distribution. GNU tar is known to work. Some operating systems come with a preinstalled version of tar that is known to have problems. For example, Mac OS X tar and Sun tar are known to have problems with long filenames. On Mac OS X, you can use the preinstalled gnutar program. On other systems with a deficient tar, you should install GNU tar first.

If you run into problems and need to file a bug report, please use the instructions in Section 1.7, 「How to Report Bugs or Problems」.

The basic commands that you must execute to install and use a MySQL binary distribution are:

shell> groupadd mysql
shell> useradd -g mysql mysql
shell> cd /usr/local
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data

Start Mysql Services (or --user=root) 
shell> bin/mysqld_safe --user=mysql &

Note

This procedure does not set up any passwords for MySQL accounts. After

following the procedure, proceed to Section 2.17, 「Post-Installation Setup and Testing」.

A more detailed version of the p

receding description for installing a binary distribution follows:

1. Add a login user and group for mysqld to run as:

   shell> groupadd mysql
   shell> useradd -g mysql mysql
   

   These commands add the mysql group and the mysql user. The syntax for useradd and groupadd may differ slightly on different versions of Unix, or they may have different names such as adduser and addgroup.

   You might want to call the user and group something else instead of mysql. If so, substitute the appropriate name in the following

   steps.

2. Pick the directory under which you want to unpack the distribution and change location into it. In the following example, we unpack the distribution under /usr/local. (The instructions, therefore, assume that you have permission to create files and directories in /usr/local. If that directory is protected, you must perform the installation as root.)

   shell> cd /usr/local
   

3. Obtain a distribution file using the instructions in Section 2.5, 「How to Get MySQL」. For a given release, binary distributions for all platforms are built from the same MySQL source distribution.

4. Unpack the distribution, which creates the installation directory. Then create a symbolic link to that directory:

   shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
   shell> ln -s full-path-to-mysql-VERSION-OS mysql
   

   The tar command creates a directory named mysql-VERSION-OS. The ln command makes a symbolic link to that directory. This lets you refer more easily to the installation directory as /usr/local/mysql.

   With GNU tar, no separate invocation of gunzip is necessary. You can replace the first line with the following alternative command to uncompress and extract the distribution:

   shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
   

5. Change location into the installation directory:

   shell> cd mysql
   

   You will find several files and subdirectories in the mysql directory. The most important for installation purposes are the bin and

   scripts subdirectories:

   • The bin directory contains cli

     ent programs and the server. You should add the full pathname of this directory to your PATH environment variable so that your shell finds the MySQL programs properly. See Section 2.21, 「Environment Variables」.

   • The scripts directory contains the mysql_install_db script used to initialize the mysql database containing the grant tables that store the server access permissions.

6. Ensure that the distribution contents are accessible to mysql. If you unpacked the distribution as mysql, no further action is required. If you unpacked the distribution as root, its contents will be owned by root. Change its ownership to mysql by executing the following commands as root in the installation directory:

   shell> chown -R mysql .
   shell> chgrp -R mysql .
   
   
   

   The first command changes the owner attribute of the files to the mysql user. The second changes the group attribute to the mysql group.

7. If you have not installed MySQL before, you must create the MySQL data directory and initialize the grant tables:

   shell> scripts/mysql_install_db --user=mysql
   

   If you run the command as root, include the --user option as shown. If you run the command while logged in as that user

   , you can omit the --user option.

   The command should create the data directory and its contents with mysql as the owner.

   After creating or updating the grant tables, you need to restart the server manually.

8. Most of the MySQL installation can be owned by root if you like. The exception is that the data directory must be owned by mysql. To accomplish this, run the following commands as root in the installation directory:

   shell> chown -R root .
   
   shell> chown -R mysql data
   
   
   

9. If you want MySQL to start automatically when you boot your machine, you can copy support-files/mysql.server to the location where your system has its startup files. More information can be found in the support-files/mysql.server script itself and in Section 2.17.2.2, 「Starting and Stopping MySQL Automatically」.

10. You can set up new accounts using the bin/mysql_setpermission script if you install the DBI and DBD::mysql Perl modules. See Section 4.6.14, 「mysql_setpermission — Interactively Set Permissions in Grant Tables」. For Perl module installation instructions, see Section 2.22, 「Perl Installation Notes」.

11. If you would like to use mysqlaccess and have the MySQL distribution in some non-standard location, you must change the location where mysqlaccess expects to find the mysql client. Edit the bin/mysqlaccess

    script at approximately line 18. Search for a line that looks like this:

    $MYSQL     = '/usr/local/bin/mysql';    # path to mysql executable
    

    Change the path to reflect the location where mysql actually is stored on your system. If you do not do this, a Broken pipe error will occur when you run mysqlaccess.

After everything has been unpacked and installed, you should test your distribution. To start the MySQL server, use the following command:

shell> bin/mysqld_safe --user=mysql &

If you run the command as root, you must use th

e --user option as shown. The value of the option is the name of the login account that you created in the first step to use for running the server. If you run the command while logged in as mysql, you can omit the --user option.

If the command fails immediately and prints mysqld ended, you can find some information in the host_name.err file in the data directory.

More information about mysqld_safe is given in Section 4.3.2, 「mysqld_safe — MySQL Server Startup Script」.

Note

The accounts that are listed in the MySQL grant tables initially have no passwords. After starting the server, you should set up passwords for them using the instructions in Section 2.17, 「Post-Installation Setup and Testing」.

Set the MySql autorun in Fedora