Mysql
Jump to navigation
Jump to search
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.
setup database
mysql_install_db --user=mysql
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'
revoke permissions
mysql -e "REVOKE ALL ON *.* FROM 'user'@'%' ; "
delete user
mysql -e "DROP USER 'user'@'%';"
Clear flush MySQL hosts cache dns
# Look at current state. use performance_schema;select host,ip from host_cache; # Clear the cache with below SQL commands then you are all set. 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';
Alternative4
mysql> update user_auth set password=md5('admin') where username='admin';
aternative5
UPDATE mysql.user SET authentication_string=password('$Passw0rd') WHERE user='$mysql_user';
backup databases
backup all databases
mysqldump --all-databases -p > /temp/mysql
backup single table
mysqldump db_name table_name > table_name.sql
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
- example command to backup single database.
DATABASE=mediawiki ; mysqldump --database ${DATABASE} --single-transaction | gzip > ${DATABASE}.${HOSTNAME}.$(date '+%Y-%m-%d_%H-%M-%S').sql.gz
restore compressed db
zcat IPadmin.1478001970.sql.gz | mysql
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 TABLES like 'catalog_category_product_index_store%';
show tables status
Show more information about tables.
mysql -D jasperserver -e "show table status like 'JIContentResource' \\G"
Show more information about one table.
show table status like 'tablename' \G
Show columns in table.
show columns from hosts;
Show which data is stored in a table in descending order limit to 100
Reverse sort order.
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;
Select column with highest value.
select * from orderreport where OrderReportId = (select max(OrderReportId) from orderreport)\G
How many rows are on the table.
select count(*) from database.table;
SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '${DATABASE}';
show value from query
select * from wp_options where option_name='home';
match multiple strings
select * from database.table where name REGEXP 'string1|2string' limit 100; WHERE (im.Status='open' or im.Status='new' or im.Status='resolved'
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 constrains on tables
show create table table show create table database.table \G
foreign keys to a table or column
SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = '${DATABASE}' AND
REFERENCED_TABLE_NAME = '${TABLE}';
set constrains
mysql -e 'ALTER TABLE Database ADD Table `Refstate_execution35` FOREIGN KEY (`state_execution_id`) REFERENCES `state_execution` (`state_execution_id`);'
get auto_increment
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database' AND TABLE_NAME = '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
start slave
START SLAVE;
stop slave
stop slave;
Show permission on database
select * from information_schema.user_privileges; SHOW GRANTS FOR 'user'@'%host.com';
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';
log verbosity before 5.7.2
SELECT @@log_warnings; SET GLOBAL log_warnings=2;
log verbosity 5.7.2
SELECT @@log_error_verbosity; SET GLOBAL log_error_verbosity=2
maxconnections in runtime.
show variables like '%connect%';
information about mysql connection
mysql -u test -e '\s'
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;
On all databases
mysql --skip-column-names -e "SELECT DISTINCT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys') ORDER BY SCHEMA_NAME;" | while read i ; do echo '***' $i ; mysql -e "use $i ; show table status;" ; done | column_tab | less
Table status on all databases.
SELECT * FROM information_schema.tables WHERE table_schema = DATABASE();
SELECT * FROM information_schema.tables WHERE table_schema IN (SELECT DISTINCT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys') ORDER BY SCHEMA_NAME);
repair myisam table
REPAIR TABLE table USE_FRM;
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.
For another version.
/usr/bin/mysqld --skip-grant-tables &
use mysql ; update user set authentication_string=password('Sw1LdtkMKnRik') where user='root'; 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, MASTER_PORT='3307';
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.
setup slave xtrabackup
https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/setting_up_replication.html
# On master. xtrabackup --backup --target-dir=/tmp/xtrabackup_$HOSTNAME xtrabackup --prepare --target-dir=/tmp/xtrabackup_$HOSTNAME SLAVE=slave.inter.net ; rsync -avpP /tmp/xtrabackup_$HOSTNAME $SLAVE:/tmp/xtrabackup_$HOSTNAME GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.30.145.85' IDENTIFIED BY 'P@ssw0rd'; FLUSH PRIVILEGES; # On slave. mysql --host=master.inter.net --user=repl --password=P@ssw0rd systemctl stop mysqld && mv /var/lib/mysql /var/lib/mysql.$(date_file) xtrabackup --move-back --target-dir=/tmp/xtrabackup_slave/xtrabackup_slave/ chown -Rh mysql:mysql /var/lib/mysql restorecon -R -v /var/lib/mysql systemctl start mysql cat /tmp/xtrabackup_*/xtrabackup_binlog_info mysql-bin.000022 476974 mysql -e "CHANGE MASTER TO MASTER_HOST='master.inter.net', MASTER_USER='repl', MASTER_PASSWORD='P@ssw0rd', MASTER_LOG_FILE='mysql-bin.000022', MASTER_LOG_POS=476974;" mysql -e "START SLAVE;"
stop slave from being slave
RESET SLAVE ALL;
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 ;
Show size of every table in database
SELECT
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;
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
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;'
truncate table
Truncate table
TRUNCATE TABLE table_name
When you want to do something that is more effective then truncate.
mysql -e 'use database;RENAME TABLE sessions TO t1;CREATE TABLE sessions LIKE t1;DROP TABLE t1;'
explain
EXPLAIN SELECT * FROM categories\G
Explain described. possible_keys=possible indexes, key=chosen index. rows=rows scanned.
+----+-------------+-----------------+--------+--------------------------------------+--------------------------------------+---------+---------------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+--------+--------------------------------------+--------------------------------------+---------+---------------------------------------+------+-------------+ | 1 | SIMPLE | activerada0_ | ALL | NULL | NULL | NULL | NULL | 8425 | NULL | | 1 | SIMPLE | ticketauto1_ | eq_ref | idx_u_ticket_automaton_status_ticket | idx_u_ticket_automaton_status_ticket | 4 | IPradar.activerada0_.ticket_id | 1 | Using where | | 1 | SIMPLE | ipmonticke2_ | eq_ref | PRIMARY | PRIMARY | 8 | IPradar.activerada0_.ticket_id | 1 | NULL | ...
innodb tables list
list innodb tables
SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';
alter table
Read fragmented innodb file from disk, Lock table, Store table back on disk without holes
ALTER TABLE table ENGINE=INNODB;
Another alternative.
OPTIMIZE TABLE prefilledautomaten.wmsreportedorders_archive;
optimize execution
SET SESSION optimizer_search_depth = 5;
mysql_upgrade
After updating mysql run the following command to update tables.
mysql_upgrade
which tables are locked
show open tables where in_use <> 0;
unlock tables
UNLOCK TABLES;
FOREIGN_KEY
Disable foreign key constrains temporary
SET FOREIGN_KEY_CHECKS=0;
Enable foreign key contrains.
SET FOREIGN_KEY_CHECKS=1;
List indexes
List index for one table.
show index from database.table;
List all indexes.
use information_schema;SELECT * FROM statistics; mysql -e "use information_schema;SELECT * FROM statistics;" | column -t -s $'\t' | less -ISRM
how much space does my index take
mysql -e "SELECT table_schema as database_name,table_name,round(index_length/1024/1024,2) as index_size
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
and table_schema not in ('information_schema', 'sys','performance_schema', 'mysql')
and table_schema = '$DATABASE'
ORDER BY index_size desc;" | column_tab | head -3
database_name table_name index_size
polar props 1094.75
polar changedescriptors 488.83
delete index
DATABASE=x793 ; TABLE=icommerce_original_price ; mysql -D $DATABASE "DROP INDEX 'IDX_ICOMMERCE_ORIGINAL_PRICE_SKU' ON $TABLE;"
password snooping
sudo tcpdump -l -i any -w - src or dst port 3306 | strings
get uniq answers
"select DISTINCT table.database from table where conn_data LIKE '%value';"
match rows with multiple equal columns
select column1, column2, column3, count(*) as NumDuplicates from database_name.table_name group by column1, column2, column3 having NumDuplicates > 1;
search for values matching null
Search for null
select column from database.table where column_line IS NULL
Search for not null
select * from datebase.table WHERE column IS NOT NULL limit 1;"
select values bigger then
select column from database.table where column >= 14586379;
load blob data
INSERT INTO table1 VALUES(1, LOAD_FILE('data.png'));
nestled queries
Loop through the output of two nestled queries.
select * from connection_data where conn_data_id IN (select conn_data_id from connection_data_attribute where conn_id = (select conn_id from IPdiscover.connection where conn_uuid = "F5122"));
mysqltuner
Download. script to optimize mysql database.
wget http://mysqltuner.pl/ -O mysqltuner.pl wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
move row between tables
A simple INSERT INTO SELECT statement:
INSERT INTO persons_table SELECT * FROM customer_table WHERE person_name = 'tom'; DELETE FROM customer_table WHERE person_name = 'tom';
date younger than
SELECT * FROM FOO WHERE MY_DATE_FIELD >= NOW() - INTERVAL 1 DAY
comment
Start with 2 -- and a space/tab. -- select...
start transaction/commit/rollback
START TRANSACTION or BEGIN start a new transaction. COMMIT commits the current transaction, making its changes permanent. ROLLBACK
slow queries
Show status of slow query log.
SHOW GLOBAL VARIABLES LIKE 'slow\_%';
Modify slow query log settings.
SET GLOBAL slow_query_log_file = '/path/to/slow_query.log';
How long is a slow query.
SET GLOBAL long_query_time = 10;
Enable slow query.
SET GLOBAL slow_query_log = 'ON'; FLUSH LOGS;
whoami
select @@hostname,database(),USER(),now();
expire_logs_days
Set value in /etc/my.cnf
[mysqld] expire_logs_days=10
What is value after change.
mysql -e "SHOW GLOBAL VARIABLES LIKE 'expire_logs_days';"
Purge old binary logs.
PURGE BINARY LOGS BEFORE (date(now()) + interval 0 second - interval 10 day);
Set expire_logs_day
SET GLOBAL expire_logs_days = 1; flush binary logs;
Expire bin logs after 12 hours. Once.
FLUSH LOGS; PURGE BINARY LOGS BEFORE NOW() - INTERVAL 12 HOUR;
queries per second
mysqladmin status
find cpu intensive queries
https://www.percona.com/blog/2020/04/23/a-simple-approach-to-troubleshooting-high-cpu-in-mysql/
pidstat -t -p $(pgrep -f "/mysqld ") | grep -Ev "$HOSTNAME|CPU Command| mysqld" | sort -k 9 -n | tail -n10 | awk '{print $5 " " $9}' | while read PID CPU ; do echo '***' $PID $CPU ; mysql -e "select * from performance_schema.threads where THREAD_OS_ID = $PID \G" ; done
slave stops fix
stop slave
stop slave;
Skip troublesome query.
set global sql_slave_skip_counter=1; start slave;
Oneliner
mysql -e "show slave status\G" > /tmp/show_slave_status.$(date '+%Y-%m-%d_%H-%M-%S') mysql -e "set global sql_slave_skip_counter=1;start slave;"
join
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
mysql_mode
SELECT @@sql_mode;
timeout
Drop mysql session after. Per session
SHOW VARIABLES LIKE 'wait_timeout';
set timeout per session.
SET wait_timeout=31536000;
Drop mysql session after. Global.
select @@GLOBAL.wait_timeout;
Set timeout globally.
SET @@GLOBAL.wait_timeout=31536000;
show functions
SHOW FUNCTION STATUS;
show stored procedures
SHOW PROCEDURE STATUS;
ssl
Is your database configured for ssl.
show variables like 'have_ssl';
collation/character set
# Available collations mysql -e 'SHOW COLLATION;' # Available characters mysql -e 'SHOW CHARACTER SET;' # The change. [mysqld] collation-server = utf8mb4_swedish_ci init-connect='SET NAMES utf8mb4' character-set-server = utf8mb4 [mysql] default-character-set=utf8mb4 # show setting mysql -e 'SELECT @@collation_server, @@character_set_server;'
Uptime
'SELECT VARIABLE_VALUE AS Uptime_seconds, NOW() AS "Now", NOW() - INTERVAL VARIABLE_VALUE SECOND AS "Up since", DATEDIFF(NOW(), NOW() - INTERVAL VARIABLE_VALUE SECOND) AS "Uptime_days" FROM performance_schema.session_status WHERE VARIABLE_NAME = "Uptime";'