As a security measure networking in the mysql server has been disabled per
default, only localhost connections will work. This is because the mysql
root user has no password in a default install and that leaves the MySQL
server open for anyone to use.
To enable networking the user has to edit or delete the
/etc/sysconfig/mysqld file.
WARNING: The host 'dhcppc0' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing all prepared tables
Fill help tables
To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h dhcppc0 password 'new-password'
See the manual for more instructions.
You can start the MySQL daemon with:
cd / ; /usr/bin/mysqld_safe &
You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
cd sql-bench ; perl run-all-tests
Please report any problems with the /usr/bin/mysqlbug script!
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
---
/etc/my.cnf config file for mysql
datadir=/var/lib/mysql
bins & scripts are in /usr/bin
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
can be in config file...
Variable Description
CXX The name of your C++ compiler (for running configure).
CC The name of your C compiler (for running configure).
CFLAGS Flags for your C compiler (for running configure).
CXXFLAGS Flags for your C++ compiler (for running configure).
DBI_USER The default username for Perl DBI.
DBI_TRACE Trace options for Perl DBI.
HOME The default path for the mysql history file is $HOME/.mysql_history.
LD_RUN_PATH Used to specify the location of libmysqlclient.so.
MYSQL_DEBUG Debug trace options when debugging.
MYSQL_HISTFILE The path to the mysql history file. If this variable is set, its value overrides the default for $HOME/.mysql_history.
MYSQL_HOST The default hostname used by the mysql command-line client.
MYSQL_PS1 The command prompt to use in the mysql command-line client.
MYSQL_PWD The default password when connecting to mysqld. Note that using this is insecure. See Section 5.8.6, ?Keeping Your Password Secure?.
MYSQL_TCP_PORT The default TCP/IP port number.
MYSQL_UNIX_PORT The default Unix socket filename; used for connections to localhost.
PATH Used by the shell to find MySQL programs.
TMPDIR The directory where temporary files are created.
TZ This should be set to your local time zone. See Section A.4.6, ?Time Zone Problems?.
UMASK_DIR The user-directory creation mask when creating directories. Note that this is ANDed with UMASK.
UMASK The user-file creation mask when creating files.
USER The default username on Windows and NetWare used when connecting to mysqld
Starting DB
as root...
/usr/bin/mysqld_safe --user=mysql &
also from drake control center
Shutting Down DB
mysqladmin --user=root -p shutdown
also from drake control center
Adding/Deleting Tables
mysqladmin --user=root create
MYSQL commands
show databases;
create database
drop database
use ;
show tables;
create table (columns...);
create table product (ProdIndex int primary key, name varchar(64), description text);
describe - to show columns...
insert into values ('',,...);
load data local infile '/path/pet.txt' into table ;
Select * from where ...
Delete * from where ...
Select count(*) from ; - gets number of rows...
alter table add column [FIRST | AFTER ];
drop column ;
---
permissions
use mysql;
select host,user from user;
show grants for 'root'@'localhost';
grant all priveleges on db.* to david@'192.58.198.0/255.255.255.0';
grant select, insert, delete on MusicCatalog.* to jim@localhost identified by 'blah';
note that some grants (ex. RELOAD) can't be offered at the db level for certain versions of mysql (only the global level).
---
http://dev.mysql.com/doc/refman/5.0/en/index.html
http://dev.mysql.com/doc/
---
The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
Which returns:
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function
or the mysql_insert_id() C API function. These functions are connection-specific, so their
return values are not affected by another connection which is also performing inserts.
Note: For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() actually return
the AUTO_INCREMENT key from the first of the inserted rows. This allows multiple-row inserts to be
reproduced correctly on other servers in a replication setup.
For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiplecolumn
index. In this case, the generated value for the AUTO_INCREMENT column is calculated as
MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when
you want to put data into ordered groups.
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
Which returns:
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+
Paging MySql Results
--------------------
mysql> \P less
mysql> PAGER set to 'less'
mysql> select foo,bar from table\G
MySql DB Backup and Restore
taken from http://www.linuxhomenetworking.com/wiki/index.php/Quick_HOWTO_:_Ch34_:_Basic_MySQL_Configuration#Recovering_.2F_Changing_Your_MySQL_Root_Password.
mysqldump --add-drop-table -u [username] -p[password] [database] > [backup_file]
mysql -u [username] -p[password] [database] < [backup_file]
Resettting Mysql pw in Linux
taken from http://wowtutorial.org/tutorial/14.html.
su -
/sbin/service mysqld stop
/usr/bin/mysqld_save --user=mysql --skip-grant-tables --skip-networking & (start mysql in safe mode)
/usr/bin/mysqladmin -u root flush-privileges password "newpass123"
/sbin/service mysqld stop
/sbin/service mysqld start (restart mysqld)
(root password should now be reset)
Showing current db status
show engine innodb status\G
\G doesn't show headings, columns etc.