Mysql: Difference between revisions

From Halfface
Jump to navigation Jump to search
(155 intermediate revisions by 3 users not shown)
Line 1: Line 1:
== Introduction ==
=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.
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 ==
=setup database=
mysql_install_db --user=mysql
=select whithout column names=
mysql --skip-column-names


=== Useful commands ===
=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;


*Create user and grant accsess.
=Change root password=
  mysql> GRANT [privileges] ON database.* TO '[user]'@'[host]' IDENTIFIED BY '[password]';
mysqladmin -u root password 'new-password'
  mysql> grant all privileges on puppetdb.* to puppet@'localhost' identified by 'puppet';FLUSH PRIVILEGES;
=revoke permissions=
  mysql -e "REVOKE ALL ON *.* FROM 'user'@'%' ; "
=delete user=
  mysql -e "DROP USER 'user'@'%';"


*Change root password.
=Clear flush MySQL hosts cache dns=
  mysqladmin -u root password 'new-password'
# 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 from mysql prompt.
=Change password=
==Alternative1.==
  use mysql;SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
  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';


*backup all databases.
==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
  mysqldump --all-databases -p > /temp/mysql
==backup single table==
mysqldump db_name table_name > table_name.sql


*Create database.
==backup certain databases.==
  mysql> create database puppetdb;
  mysqldump --opt -uroot -p --databases IPmon IPdiscover IPcollector > all.sql


*Delete mysql database.
==backup structure of dabases.==
  mysql> drop database puppetdb;
  mysqldump --compact --no-data --all-databases > /tmp/database_structure.txt


*Delete mysql database table.
==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;
  drop table if exists recipes;


*Create table.
=Delete row=
This creates table with thwo colums, id with datatype int, and data of the type varchar.
delete FROM user WHERE id IN (2);
  mysql> create table testtable (id int, data varchar(100));
 
  mysql> CREATE TABLE developers ( name VARCHAR(128), email VARCHAR(128), job VARCHAR(128));
=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.
*Connect to mysql database.
  mysql -u root -p password -h mysqlhost
  mysql -u root -p password -h mysqlhost


*Show databases.
=Show databases.=
  mysql> show databases;
  show databases;


*Choose database to work with.
=Choose database to work with.=
  mysql> use puppetdb;
  use puppetdb;


*Show tables.
=Show tables=
  mysql> show tables;
  show tables;
SHOW TABLES like 'catalog_category_product_index_store%';


*Show columns in table.
=show tables status=
  mysql> show columns from hosts;
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 which data is stored in a table in descending order limit to 100.
=Show columns in table.=
  mysql> select * from hosts order by id desc limit 100;
  show columns from hosts;


*Show all data from table.
=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;
  mysql> select * from hosts;


*Show values from one column.
=Show all ip in sort ascending order=
  mysql> select id from hosts;
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


*Show selected colums.
=How many rows are on the table.=
  mysql> select name,ip from hosts;
select count(*) from database.table;
  SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '${DATABASE}';


*Show all ip in ascending order.
=show value from query=
  mysql> select * from hosts order by ip asc;
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'


*Show column page_title in table page where page_counter is equal to 17.
=change value in database=
mysql> select page_title from page where page_counter='17';
  UPDATE wp_options SET option_value='http://www.ongamenetwork.com' WHERE option_name='home';
  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
=Run sql code. Offen used to setup database.=
  mysql> select max(page_counter) from page;
  source /file             


*How many rows are on the table.
=Run command in system.=
  mysql> select count(page_counter) from page;
  system command
mysql> select count(*) from page;


*What does it all mean?
=Shows status of database server=
  select * from wp_options where option_name='home';
  status;


*What does it all mean?
=Show information about table=
  UPDATE wp_options SET option_value='http://www.ongamenetwork.com' WHERE option_name='home';
  describe table;


*Commands.
=Show more information about table, show constrains on tables=
  mysql> exit;quit;                # exit mysql tool.
  show create table table
  mysql> help;                    # Show help
  show create table database.table \G
mysql> source /file              # Run sql code. Offen used to setup database.
=foreign keys to a table or column=
  mysql> system command            # Run command in system.
  SELECT
  mysql> status;                  # Shows status of database server.
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
  mysql> describe table;           # Show information about table.
  FROM
  mysql> show create table table  # Show more information about table.
  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`);'


*Put some data into a table.
=get auto_increment=
  mysql> INSERT INTO testtable (id,data) VALUES ('66','The beast whas here');
  SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database' AND TABLE_NAME = 'table';"


*Change id value 17 to 18 in testtable
=Put some data into a table=
  mysql> UPDATE testtable set id=18 where id=17 ;
  INSERT INTO testtable (id,data) VALUES ('66','The beast whas here');


*Show master/slave status.
=Change id value 17 to 18 in testtable=
  mysql> show master status \G
  UPDATE testtable set id=18 where id=17 ;


*Show master/slave status.
=Show master/slave status.=
  mysql> show slave status \G
  show master status \G


*Show permission on database.
=Show master/slave status.=
  mysql> show grants for onzone@localhost;
  show slave status \G
=start slave=
START SLAVE;
=stop slave=
stop slave;


*Who has access from which machine.
=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;
  use mysql;SELECT host,user FROM user;


*Has a user rights.
=Import sql script to database.=
  SELECT host,user,select_priv,insert_priv FROM user;
  mysql ongamenetwork < /tmp/ongamenetwork.new.sql


*Has a user rights on a particular database.
=Delete logs=
  SELECT host,db,user,select_priv,insert_priv FROM db;
# 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


===Reset mysql root password===
=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.
  Stop the MySql server.
  /usr/bin/mysqld_safe --skip-grant-tables &
  /usr/bin/mysqld_safe --skip-grant-tables &
Line 121: Line 227:
  update user set password = password('.......') where user = 'root' and host='localhost';
  update user set password = password('.......') where user = 'root' and host='localhost';
  quit
  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.
  Restart the server and run as normal.


==Data types==
=Data types=
*Numerical, signed=could be negative,unsigned
==Numerical, signed=could be negative,unsigned==
  type Maxvalue
  type Maxvalue
  tinyint 255
  tinyint 255
Line 131: Line 241:
  int 4294967295
  int 4294967295
  bigint 18446744073709551615
  bigint 18446744073709551615
*Strings
==Strings==
  type Maxvalue
  type Maxvalue
  varchar 255
  varchar 255
Line 139: Line 249:
  mediumblob,mediumtext 16777216
  mediumblob,mediumtext 16777216
  longblob,longtext 4294967296
  longblob,longtext 4294967296
*Special
==Special==
  date
  date
  timestamp
  timestamp


*Access types.
=Access types.=
  ALL - Gives the all privilege control for the database
  ALL - Gives the all privilege control for the database
  CREATE - Allows users to create tables
  CREATE - Allows users to create tables
Line 152: Line 262:
  GRANT OPTION - Allows users to grant privileges
  GRANT OPTION - Allows users to grant privileges


==auto submit password==
=auto submit password=
.my.cnf
.my.cnf
  [client]
  [client]
Line 158: Line 268:
  pass=password  
  pass=password  


== Web resources ==
=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';
 
[[Category:Applications]]
[[Category:Unix]]

Revision as of 12:54, 12 January 2023

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
  1. 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';