Mysql: Difference between revisions
Line 287: | Line 287: | ||
SHOW CREATE TABLE table; | SHOW CREATE TABLE table; | ||
=kill query= | =kill query= | ||
KILL QUERY "ID" | KILL QUERY "ID"; | ||
=Fragmentation= | =Fragmentation= | ||
data_free are the holes in the database. | data_free are the holes in the database. |
Revision as of 08:14, 11 May 2016
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.
select whithout column names
mysql --skip-column-names
Create user and grant accsess.
GRANT [privileges] ON database.* TO '[user]'@'[host]' IDENTIFIED BY '[password]'; grant all privileges on puppetdb.* to puppet@'localhost' identified by 'puppet';FLUSH PRIVILEGES;
Change root password
mysqladmin -u root password 'new-password'
flush dns
FLUSH HOSTS
Change password
==Alternative1.
use mysql;SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
Alternative2.
update mysql.user set password = password() where user = 'username';
Alternative3.
UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE User='user-name-here' AND Host='host-name-here';
- Change password for cacti admin to admin
mysql> update user_auth set password=md5('admin') where username='admin';
backup databases
backup all databases
mysqldump --all-databases -p > /temp/mysql
backup certain databases.
mysqldump --opt -uroot -p --databases IPmon IPdiscover IPcollector > all.sql
backup structure of dabases.
mysqldump --compact --no-data --all-databases > /tmp/database_structure.txt
dump database in innodb consistent way.
mysqldump --single-transaction -u --all-databases > /temp/alldb_june23.sql
Create database
create database puppetdb;
Delete mysql database
drop database puppetdb;
Delete mysql database table
drop table if exists recipes;
Delete row
delete FROM user WHERE id IN (2);
Create table. This creates table with two colums, id with datatype int, and data of the type varchar.
create table testtable (id int, data varchar(100)); 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.
show databases;
Choose database to work with.
use puppetdb;
Show tables
show tables;
Show columns in table.
show columns from hosts;
Show which data is stored in a table in descending order limit to 100
select * from hosts order by id desc limit 100;
Show all data from table
mysql> select * from hosts;
Show all ip in sort ascending order
mysql> select * from hosts order by ip asc;
Show column where page_counter is max in table page. Other values are min, avg, sum
select max(page_counter) from page;
How many rows are on the table.
select count(page_counter) from page; select count(*) from page;
show value from query
select * from wp_options where option_name='home';
change value in database
UPDATE wp_options SET option_value='http://www.ongamenetwork.com' WHERE option_name='home';
Run sql code. Offen used to setup database.
source /file
Run command in system.
system command
Shows status of database server
status;
Show information about table
describe table;
Show more information about table
show create table table
Put some data into a table
INSERT INTO testtable (id,data) VALUES ('66','The beast whas here');
Change id value 17 to 18 in testtable
UPDATE testtable set id=18 where id=17 ;
Show master/slave status.
show master status \G
Show master/slave status.
show slave status \G
Show permission on database
select * from information_schema.user_privileges;
Who has access from which machine.
use mysql;SELECT host,user FROM user;
Import sql script to database.
mysql ongamenetwork < /tmp/ongamenetwork.new.sql
Delete logs
# List logs. show master logs; # Delete oldest logs. purge master logs to 'logfil';
maxconnections in runtime.
show variables like '%connect%';
What is happening in database. Who is connected to database
show processlist; SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; mysql -e 'SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where NOT COMMAND="Sleep";'
database usage. Is database using myisam or innodb
show table status;
show date
select CURRENT_TIMESTAMP;
show timezone
show variables like '%time%'
mysql server version
select version ();
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
auto submit password
.my.cnf
[client] user=user_name pass=password
replication master slave
Setting the Replication Master Configuration
/etc/my.cnf
[mysqld] log-bin=mysql-bin server-id=1
To get the master status information, follow these steps: Start the command line client and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement: mysql> FLUSH TABLES WITH READ LOCK; Leave the client from which you issued the FLUSH TABLES statement running so that the read lock remains in effect. If you exit the client, the lock is released.
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 98 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
The File column shows the name of the log and Position shows the offset within the file. In this example, the binary log file is mysql-bin.000001 and the offset is 98. Record these values. You need them later when you are setting up the slave. They represent the replication coordinates at which the slave should begin processing new updates from the master.
If the master has been running previously without binary logging enabled, the log name and position values displayed by SHOW MASTER STATUS or mysqldump --master-data will be empty. In that case, the values that you need to use later when specifying the slave's log file and position are the empty string () and 4.
mysqldump --all-databases --lock-all-tables >/tmp/dbdump.db
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.7.%' IDENTIFIED BY 'slavepass';
Setting the Replication Slave Configuration
/etc/my.cnf
[mysqld] server-id=2
Import the dump file:
shell> mysql < /tmp/dbdump.db
mysql> CHANGE MASTER TO MASTER_HOST='192.168.7.30', MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=98;
Start the slave threads:
mysql> START SLAVE;
Once a slave is replicating, you can find in its data directory one file named master.info and another named relay-log.info.
Test to verify functionality of slave database
the existence of these three lines verifies that the slave is functioning well.
mysql -e 'show slave status\G' |grep -E 'Slave_IO_Running:|Slave_SQL_Running:|Seconds_Behind_Master:' Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0
Usage of database files/size
This query will output Data_length and Index_length for each table in your database. If you add them all together you can get the size used for your particular database.
show table status;
Usage of database.
SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;
pager
Put output to file instead standard output.
\P cat > /tmp/tmp
fix database/check database
All databases.
mysqlcheck -c -u root -ppassword --all-databases
Check one database
mysqlcheck -c database -u root -ppassword
look at health of slave database
select @@hostname,now();show variables like 'read_only';show slave status\G
mysqld man page
/usr/libexec/mysqld --verbose --help
last row
SELECT fields FROM table ORDER BY id DESC LIMIT 1;
enable disable full log
# WARNING. generates lot of data and slow down database. Enable general_log_file. Logs each queury against database. mysql -e "SET global log_output = 'FILE'; SET global general_log_file='queries.log'; SET global general_log = 1;"
# can be turned off with mysql -e "SET global general_log = 0;"
# Look at queries tail -f /apps/mysql/data/queries.log
# Clean log. :>/apps/mysql/data/queries.log
# If you dont find logfile do. Has to be enabled at the time. lsof | grep /queries.log
show constrains on tables
SHOW CREATE TABLE table;
kill query
KILL QUERY "ID";
Fragmentation
data_free are the holes in the database.
mysql -e 'select ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables where DATA_FREE > 0;'