Mysql: Difference between revisions
Jump to navigation
Jump to search
Line 13: | Line 13: | ||
*Change root password. | *Change root password. | ||
mysqladmin -u root password 'new-password' | mysqladmin -u root password 'new-password' | ||
*Change password from mysql prompt. | |||
use mysql;SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere'); | |||
*backup all databases. | *backup all databases. |
Revision as of 12:04, 8 April 2008
Introduction
In the structure of MySQL, there are databases, tables, records, and fields. Databases hold together tables, tables hold together records, records hold together fields, which contain the actual information.
Mysql
Useful commands
- Create user and grant accsess.
mysql> GRANT [privileges] ON database.* TO '[user]'@'[host]' IDENTIFIED BY '[password]'; mysql> grant all privileges on puppetdb.* to puppet@'localhost' identified by 'puppet';FLUSH PRIVILEGES;
- Change root password.
mysqladmin -u root password 'new-password'
- Change password from mysql prompt.
use mysql;SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
- backup all databases.
mysqldump --all-databases -p > /temp/mysql
- Create database.
mysql> create database puppetdb;
- Delete mysql database.
mysql> drop database puppetdb;
- Create table.
This creates table with thwo colums, id with datatype int, and data of the type varchar. mysql> create table testtable (id int, data varchar(100)); mysql> CREATE TABLE developers ( name VARCHAR(128), email VARCHAR(128), job VARCHAR(128));
- Connect to mysql database.
mysql -u root -p password -h mysqlhost
- Show databases.
mysql> show databases;
- Choose database to work with.
mysql> use puppetdb;
- Show tables.
mysql> show tables;
- Show columns in table.
mysql> show columns from hosts;
- Show which data is stored in a table in descending order limit to 100.
mysql> select * from hosts order by id desc limit 100;
- Show all data from table.
mysql> select * from hosts;
- Show values from one column.
mysql> select id from hosts;
- Show selected colums.
mysql> select name,ip from hosts;
- Show all ip in ascending order.
mysql> select * from hosts order by ip asc;
- Show column page_title in table page where page_counter is equal to 17.
mysql> select page_title from page where page_counter='17'; mysql> select page_title from page where page_counter='17' and page_is_new='1'; # and page_is_new equal to 1.
- Show column where page_counter is max in table page. Other values are min, avg, sum
mysql> select max(page_counter) from page;
- How many rows are on the table.
mysql> select count(page_counter) from page; mysql> select count(*) from page;
- What does it all mean?
select * from wp_options where option_name='home';
- What does it all mean?
UPDATE wp_options SET option_value='http://www.ongamenetwork.com' WHERE option_name='home';
- Commands.
mysql> exit;quit; # exit mysql tool. mysql> help; # Show help mysql> source /file # Run sql code. Offen used to setup database. mysql> system command # Run command in system. mysql> status; # Shows status of database server. mysql> describe table; # Show information about table. mysql> show create table table # Show more information about table.
- Put some data into a table.
mysql> INSERT INTO testtable (id,data) VALUES ('66','The beast whas here');
- Change id value 17 to 18 in testtable
mysql> UPDATE testtable set id=18 where id=17 ;
- Show master/slave status.
mysql> show master status \G
- Show master/slave status.
mysql> show slave status \G
- Show permission on database.
mysql> show grants for onzone@localhost;
- Who has access from which machine.
SELECT host,user FROM user;
- Has a user rights.
SELECT host,user,select_priv,insert_priv FROM user;
- Has a user rights on a particular database.
SELECT host,db,user,select_priv,insert_priv FROM db;
Reset mysql root password
Stop the MySql server. /usr/bin/mysqld_safe --skip-grant-tables & mysql -h localhost use mysql update user set password = password('.......') where user = 'root' and host='localhost'; quit Restart the server and run as normal.
Data types
- Numerical, signed=could be negative,unsigned
type Maxvalue tinyint 255 smallint 65535 mediumint 16777215 int 4294967295 bigint 18446744073709551615
- Strings
type Maxvalue varchar 255 char 255 tinyblob,tinytext 256 blob,text 65536 mediumblob,mediumtext 16777216 longblob,longtext 4294967296
- Special
date timestamp
- Access types.
ALL - Gives the all privilege control for the database CREATE - Allows users to create tables SELECT - Allows users to query tables INSERT - Allows users to insert data into a table SHOW DATABASES - Allows users to see a list of databases USAGE - User has no privileges GRANT OPTION - Allows users to grant privileges