View source for mysql
{{anchor href="top"}}
Hits : **{{hits}}**
== MySQL References ==
{{ToC from="h2" to="h3"}}
=== Install ===
http://www.mysql.com
** MySQL 5.1.39 **
%%(code)
# cd /opt
# wget -c http://ftp.ntua.gr/pub/databases/mysql/Downloads/MySQL-5.1/mysql-5.1.39-linux-i686-glibc23.tar.gz
# tar zxvf mysql-5.1.39-linux-i686-glibc23.tar.gz
# ln -s mysql-5.1.39-linux-i686-glibc23/ mysql
%%
%%(html)
groupadd mysql
useradd -g mysql mysql
cd /usr/local/mysql
./scripts/mysql_install_db --user=mysql
chown -Rv root .
chown -Rv mysql data
chgrp -Rv mysql .
./bin/mysqld_safe --user=mysql &
./bin/mysql
use mysql
UPDATE `mysql`.`user` SET `Password` = OLD_PASSWORD( 'your password here' ) WHERE `user`.`User` = 'root';
FLUSH PRIVILEGES;
exit
./bin/mysql -p
%%
((#top))
-----------
===mysql recover root passwd===
%%(html)
/etc/init.d/mysqld stop
/usr/bin/mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
use mysql;
UPDATE user SET Password=PASSWORD("your password here") WHERE User="root";
exit
# kill all the mysql processes
/etc/init.d/mysqld stop
# Start MySQL again
/etc/init.d/mysqld start
%%
((#top))
-----------
===count rows===
%%(sql)
SELECT COUNT(*) FROM table_name;
%%
((#top))
-----------
=== MySQL Version ===
%%(sql)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.22 |
+-----------+
1 row in set (0.00 sec)
%%
((#top))
-----------
=== MySQL Date - Time ===
%%(sql)
mysql> SELECT CURRENT_DATE, CURRENT_TIME;
+--------------+--------------+
| CURRENT_DATE | CURRENT_TIME |
+--------------+--------------+
| 2007-10-15 | 10:36:16 |
+--------------+--------------+
1 row in set (0.00 sec)
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2007-10-15 10:36:56 |
+---------------------+
1 row in set (0.00 sec)
%%
((#top))
-----------
=== MySQL Databases ===
%%(sql)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ebal |
+--------------------+
2 rows in set (0.03 sec)
mysql> use ebal;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
%%
((#top))
-----------
=== MySQL Change Password ===
%%(sql)
mysql> UPDATE user SET Password=OLD_PASSWORD('test') WHERE User='root' AND Host='localhost';
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
%%
((#top))
-----------
=== Data Types ===
http://dev.mysql.com/doc/refman/5.1/en/data-types.html
Chapter 10. Data Types
**Numeric**
#|
|| Type | Bytes | Minimum Value | Maximum Value ||
|| | | (Signed/Unsigned) | (Signed/Unsigned) ||
|| TINYINT | 1 | -128 | 127 ||
|| | | 0 | 255 ||
|| SMALLINT | 2 | -32768 | 32767 ||
|| | | 0 | 65535 ||
|| MEDIUMINT | 3 | -8388608 | 8388607 ||
|| | | 0 | 16777215 ||
|| INT | 4 | -2147483648 | 2147483647 ||
|| | | 0 | 4294967295 ||
|| BIGINT | 8 | -9223372036854775808 | 9223372036854775807 ||
|| | | 0 | 18446744073709551615 ||
|#
http://en.wikipedia.org/wiki/Signed_number_representations
** String **
#|
|| 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 ||
|#
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
((#top))
-----------
=== Ελληνικά ===
greek in mysql
Ελληνικά στην MySQL, δλδ ελληνικό όνομα στην βάση δεδομένων, στον πίνακα, στα πεδία και στα περιεχόμενα !!!
%%(code)
CREATE DATABASE `ελλας` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE IF NOT EXISTS `ελληνικά` (
`όνομα` varchar(25) NOT NULL,
`επώνυμο` varchar(50) NOT NULL,
`τηλ` char(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `ελληνικά` (`όνομα`, `επώνυμο`, `τηλ`) VALUES
('ευάγγελος', 'μπαλάσκας', '693'),
('βαγγέλης', 'μπαλάσκας', '697');
%%
από την κονσόλα της MySQL:
για να μάθουμε τι έχει :
%%(code)
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
%%
για να θέσουμε :
%%(code)
SET NAMES utf8;
set character_set_client = utf8;
set character_set_connection = utf8;
set character_set_database = utf8;
set character_set_results = utf8;
set collation_connection = utf8_general_ci;
set collation_database = utf8_general_ci;
set collation_server = utf8_general_ci;
%%
=== Διεργασίες ===
%%
show full processlist;
%%
((#top))
-----------
=== create database ===
%%(sql)
# mysqladmin create database -p
# mysql -p
mysql> use mysql;
mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON database.* TO 'pastebin'@'localhost' WITH GRANT OPTION;
%%
=== Show Process List ===
%%(sql)
select * from information_schema.processlist ;
%%