Mysql

From Halfface
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.

Mysql

Useful commands

  • select whithout column names
mysql --skip-column-names
  • 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'

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.
mysql> create database puppetdb;
  • Delete mysql database.
mysql> 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 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 sort 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> select user,host from mysql.user;
mysql> select user,password,host from mysql.user;
mysql> show grants for onzone@localhost;
  • Who has access from which machine.
use mysql;SELECT host,user FROM user;
  • Has a user rights.
SELECT host,user,select_priv,insert_priv FROM user;
show grants for onzone@'192.168.180.181';
  • Has a user rights on a particular database.
SELECT host,db,user,select_priv,insert_priv FROM db;
  • 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
  • database usage. Is database using myisam or innodb?
mysql> show table status;
  • show date
mysql> select CURRENT_TIMESTAMP;
  • show timezone.
mysql> show variables like '%time%'
  • mysql server version
mysql> 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

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
Retrieved from "https://halfface.se/wiki/index.php?title=Mysql&oldid=12624"