show databases;
use db
show tables
describe table
mysql database < text_file # to read commands from a text file.
################################## Sample Perl/DBI code
use DBI();
my $dbh = DBI->connect("DBI:mysql:dbname;localhost",
"user", "pass",
{'RaiseError' => 1});
my $sql = qq#select * from table#;
my $sth = $dbh->prepare($sql);
$sth->execute();
while (my $ref = $sth->fetchrow_arrayref())
{
print "$ref->[0]\n";
}
$sth->finish();
$dbh->disconnect();
###################################
mysqladmin reload
mysqlaccess user db
mysqlshow
+-----------+
| Databases |
+-----------+
| mysql |
| test |
+-----------+
mysqladmin -p create DATABASE
Data Directory: /var/lib/mysql
mysqladmin Ver 8.0 Distrib 3.22.32, for pc-linux-gnu on i686
TCX Datakonsult AB, by Monty
This software comes with NO WARRANTY: see the file PUBLIC for details.
Administer program for the mysqld demon
Usage: mysqladmin [OPTIONS] command command....
-#, --debug=... Output debug log. Often this is 'd:t:o,filename`
-f, --force Don't ask for confirmation on drop database; with
multiple commands, continue even if an error occurs
-?, --help Display this help and exit
-C, --compress Use compression in server/client protocol
-h, --host=# Connect to host
-p, --password[=...] Password to use when connecting to server
If password is not given it's asked from the tty
-P --port=... Port number to use for connection
-i, --sleep=sec Execute commands again and again with a sleep between
-r, --relative Show difference between current and previous values
when used with -i. Currently works only with
extended-status
-s, --silent Silently exit if one can't connect to server
-S, --socket=... Socket file to use for connection
-t, --timeout=... Timeout for connection to the mysqld server
-u, --user=# User for login if not current user
-V, --version Output version information and exit
-w, --wait[=retries] Wait and retry if connection is down
Default options are read from the following files in the given order:
/etc/my.cnf ~/.my.cnf
The following groups are read: mysqladmin client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit
--no-defaults Don't read default options from any options file
--defaults-file=# Only read default options from the given file #
Where command is a one or more of: (Commands may be shortened)
create databasename Create a new database
drop databasename Delete a database and all its tables
extended-status Gives an extended status message from the server
flush-hosts Flush all cached hosts
flush-logs Flush all logs
flush-status Clear status variables
flush-tables Flush all tables
flush-privileges Reload grant tables (same as reload)
kill id,id,... Kill mysql threads
password new-password Change old password to new-password
ping Check if mysqld is alive
processlist Show list of active threads in server
reload Reload grant tables
refresh Flush all tables and close and open logfiles
shutdown Take server down
status Gives a short status message from the server
variables Prints variables available
version Get version info from server
|
Who opens the file for importing, the server(mysqld) or the client(mysql)??? |
- Via a SQL Command
A) The Server process (mysqld) loads the file.
mysql> load data infile '/home/data/mytable.dat' into table mytable;
- NOTE: If relative file is specified, it will be relative to the server root and not the client directory!! So if you specified only a filename it would look in /var/lib/mysql. Since in this example we specified the full path ( and the file is readable by mysqld ) everything works. Even if the client and server are the same machine, needs absolute file path. If "rich" was used it would look the file in a directory relative to the database files.
- FILE PRIVILEGE ( Table:mysql.user File_priv ) NEEDED TO LOAD FILE
- Fastest way to load data
B) The Client process (mysql) loads the file.
mysql> load data local infile 'mytable.dat' into table mytable;
- Note the use of the keyword local.
- The directory where the file is located is relative to the client.
- FILE PRIVILEGE not needed to load file
- Via a mysqlimport
mysqlimport
use the --local option if importing from a local file
mysqlimport Ver 1.3 Distrib 3.22.25, for pc-linux-gnu (i586)
Monty & Jani. This software is in public Domain
This software comes with ABSOLUTELY NO WARRANTY
Loads tables from text files in various formats. The base name of the
text file must be the name of the table that should be used.
If one uses sockets to connect to the MySQL server, the server will open and
read the text file directly. In other cases the client will open the text
file. The SQL command 'LOAD DATA INFILE' is used to import the rows.
Usage: mysqlimport [OPTIONS] database textfile...
-#, --debug[=...] Output debug log. Often this is 'd:t:o,filename`
-?, --help Displays this help and exits.
-C, --compress Use compression in server/client protocol
-d, --delete Deletes first all rows from table.
-f, --force Continue even if we get an sql-error.
-h, --host=... Connect to host.
-i, --ignore If duplicate unique key was found, keep old row.
-l, --lock-tables Lock all tables for write.
-L, --local Read all files through the client
-p, --password[=...] Password to use when connecting to server.
If password is not given it's asked from the tty.
-P, --port=... Port number to use for connection.
-r, --replace If duplicate unique key was found, replace old row.
-s, --silent Be more silent.
-S, --socket=... Socket file to use for connection.
-u, --user=# User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
--fields-terminated-by=...
Fields in the textfile are terminated by ...
--fields-enclosed-by=...
Fields in the importfile are enclosed by ...
--fields-optionally-enclosed-by=...
Fields in the i.file are opt. enclosed by ...
--fields-escaped-by=...
Fields in the i.file are escaped by ...
--lines-terminated-by=...
Lines in the i.file are terminated by ...
mysql
mysql Ver 9.16 Distrib 3.21.33, for pc-linux-gnu (i586)
By TCX Datakonsult AB, by Monty
This software comes with ABSOLUTELY NO WARRANTY.
Usage: mysql [OPTIONS] [database]
-A, --no-auto-rehash No automatic rehashing. One has to use 'rehash' to
get table and field completion. This gives a quicker
start of mysql.
-B, --batch Print results with a tab as separator, each row on
a new line. Doesn't use history file
-#, --debug=... Output debug log. Often this is 'd:t:o,filename`
-T, --debug-info Print some debug info at exit
-e, --execute=... Execute command and quit.(--batch is implicit)
-f, --force Continue even if we get an sql error.
-?, --help Display this help and exit
-h, --host=... Connect to host
-n, --unbuffered Flush buffer after each query
-O, --set-variable var=option
Give a variable an value. --help lists variables
-p[password], --password[=...]
Password to use when connecting to server
If password is not given it's asked from the tty.
-P --port=... Port number to use for connection
-q, --quick Don't cache result, print it row by row. This may
slow down the server if the output is suspended.
Doesn't use history file
-r, --raw Write fields without conversion. Used with --batch
-s, --silent Be more silent.
-S --socket=... Socket file to use for connection
-t --table=... Output in table format
-u, --user=# User for login if not current user
-v, --verbose Write more (-v -v -v gives the table output format)
-V, --version Output version information and exit
-w, --wait Wait and retry if connection is down
Possibly variables to option --set-variable (-O) are:
net_buffer_length current value: 16384l
mysqldump
mysqldump Ver 7.1 Distrib 3.22.32, for pc-linux-gnu (i686)
By Igor Romanenko, Monty, Jani & Sinisa. This software is in public Domain
This software comes with ABSOLUTELY NO WARRANTY
Dumping definition and data mysql database or table
Usage: mysqldump [OPTIONS] database [tables]
-a, --all Include all MySQL specific create options
-#, --debug=... Output debug log. Often this is 'd:t:o,filename`
-?, --help Display this help message and exit.
-c, --complete-insert Use complete insert statements.
-C, --compress Use compression in server/client protocol
-e, --extended-insert Allows utilization of the new, much faster
INSERT syntax
--add-drop-table Add a 'drop table' before each create
--add-locks Add locks around insert statements
--allow-keywords Allow creation of column names that are keywords
--delayed-insert Insert rows with INSERT DELAYED
-F --flush-logs Flush logs file in server before starting dump
-f, --force Continue even if we get an sql-error.
-h, --host=... Connect to host.
-l, --lock-tables Lock all tables for read.
-t, --no-create-info Don't write table creation info.
-d, --no-data No row information.
-O, --set-variable var=option
give a variable a value. --help lists variables
--opt Same as --add-drop-table --add-locks --all
--extended-insert --quick --lock-tables
-p, --password[=...] Password to use when connecting to server.
If password is not given it's solicited on the tty.
-P, --port=... Port number to use for connection.
-q, --quick Don't buffer query, dump directly to stdout.
-Q, --quote-names Quote table and column names with `
-S, --socket=... Socket file to use for connection.
-T, --tab=... Creates tab separated textfile for each table to
given path. (creates .sql and .txt files).
NOTE: This only works if mysqldump is run on
the same machine as the mysqld daemon.
-u, --user=# User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
-w, --where= dump only selected records; QUOTES mandatory!
EXAMPLES: "--where=user='jimf'" "-wuserid>1" "-wuserid<1"
Use -T (--tab=...) with --fields-...
--fields-terminated-by=...
Fields in the textfile are terminated by ...
--fields-enclosed-by=...
Fields in the importfile are enclosed by ...
--fields-optionally-enclosed-by=...
Fields in the i.file are opt. enclosed by ...
--fields-escaped-by=...
Fields in the i.file are escaped by ...
--lines-terminated-by=...
Lines in the i.file are terminated by ...
Default options are read from the following files in the given order:
/etc/my.cnf ~/.my.cnf
The following groups are read: mysqldump client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit
--no-defaults Don't read default options from any options file
--defaults-file=# Only read default options from the given file #
Possible variables for option --set-variable (-O) are:
max_allowed_packet current value: 25165824
net_buffer_length current value: 1047551
insert into
host(host,db,Select_priv,Insert_priv,Update_priv,
Delete_priv,Create_priv,Drop_priv,Grant_priv,References_priv,Index_priv,Alter_priv)
values('localhost','%','Y', 'Y', 'Y', 'Y', 'Y', 'Y','Y','Y','Y','Y');
INSERT INTO user ( Host, User, Password ) VALUES ( '%' ,'user',password('pass'));
INSERT INTO user ( Host, User, Password, Select_priv ) VALUES ( '%' ,'phfh',password('phfh1'),"Y");
INSERT INTO db ( Host,DB,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,R
eferences_priv,Index_priv,Alter_priv)
VALUES ( '%','db','user',"Y","Y","Y","Y","Y","Y","Y","Y","Y")
select Host,Db,User,Select_priv,Insert_priv,Update_priv from db;
select Host,User,Password,Select_priv,Insert_priv,Update_priv,File_priv from user;
Field Value Action
----------------------------------------
Host % any host
Host blank consult host table
DB blank any database
DB % any database
User blank anonymous user
mysql> select * from db;
+------+------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+
| Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv |
+------+------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+
| % | test | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
+------+------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+
mysql> select * from user;
+-----------------------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv |
+-----------------------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
| localhost | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| localhost.localdomain | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| localhost | | | N | Y | N | N | N | N | N | N | N | Y | N | Y | Y | Y |
| localhost.localdomain | | | N | N | N | N | N | N | N | N | N | N | N | Y | Y | Y |
+-----------------------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+