Posts tagged mysql

mysql increase redo log size
posted on 2017-03-03 18:46

For tuning mysql innodb-wise it helps to allocate enough space in ram for the tables via the buffer pool. For this innodb_buffer_pool_size is to be increased as much as possible and needed, depending on your database size. mysqltuner may help discerning the minimum size, along with other hints.

Besides that the redo log has to be adjusted to about 1/6 of the buffer pool via innodb_log_file_size.

To fix this, there are more steps needed, else the mysql instance will not start properly after the next restart.

  1. start mysql client
  2. SET GLOBAL innodb_fast_shutdown=0
  3. \q
  4. service mysql stop
  5. fix both mentioned config vars, best in /etc/mysql/conf.d/zz_yourconfig.cnf
  6. rm /var/lib/mysql/ib_logfile*
  7. service mysql start
mysql describe all tables from database
posted on 2017-01-23 12:51

This can be used directly in bash:

DB=your_database_name_here; for i in $(mysql $DB -Ne 'show tables' | cat); do echo; echo $i; mysql $DB -te "describe $i"; done

Just adjust your database.

postfixadmin update and php7
posted on 2016-12-09 08:52

After a dist upgrade from ubuntu 14.04 to 16.04 and updating to php7 the postfixadmin in version 2.3.7 stopped working due to php5 modules naturally being amiss. These were the steps I took so the upgrade to postfixadmin 3.0 worked. The major roadblocks were the changed php modules and updating the mysql tables during the update.

  • mkdir /root/postfixadmin-backup; cd /root/postfixadmin-backup
  • mysqldump <POSTFIX-DB-NAME> > <POSTFIX-DB-NAME>.sql so I'd have a database backup.
  • Copied the old htdocs to /root/htdocs, to have a webdata backup in case I'd fuck up renaming the htdocs later.
  • cd /path/to/postfixadmin/webroot
  • The apache setup was fine so already, so I renamed the old htdocs (docroot), created a new one and extracted the newly downloaded postfixadmin there.
  • Copied the old config.inc.php over into the new docroot, named config.local.php.disabled so it would not get read upon opening the postfixadmin webinterface.
  • Copied the new config.inc.php to config.local.php.
  • diff config.local.php.disabled config.inc.php to show me the differences to the new installation.
  • Adjust database settings and the other settings I wanted to conserve.
  • Browser: https://domain.of.postfixamin/setup.php

Then it showed:

...

DEBUG INFORMATION:
Invalid query: Invalid default value for 'created'

...

Upon using https://domain.of.postfixadmin/setup.php?debug=1 I found out that that was related to the vacation table in the database. To be exact, this line shown by describe vacation at the mysql client prompt:

| created       | datetime     | NO   |     | 0000-00-00 00:00:00 |       |

The ALTER TABLE statement could not run the change due to mysql's strict mode being active, and a date of 0000-00-00 00:00:00` being forbidden.

At first I tried to alter the table to something like 1970-01-01 01:01:01 but that wouldn't work.

mysql's strict mode is controlled by the variable sql_mode:

mysql> show variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

To set this variable upon mysql's starting process, I edited /etc/mysql/my.cnf and added under the [mysqld] section:

sql_mode = ''

Then service mysql stop, service mysql start and upon reopening https://domain.of.postfixamin/setup.php the setup went through and all was well again in postfix-land.

I removed the sql_mode parameter from /etc/mysql/my.cnf again, service mysql stop, service mysql start and I was free to go off to other ventures again.

mysql slow query log
posted on 2016-11-07 10:30

To enable mysql's slow query log:

show variables like '%query%';
show variables like '%slow%';
set global slow_query_log = 'on';
show variables like '%slow%';
flush logs; 

set global long_query_time = 1;
set global long_query_time = 5;
flush logs;

Look up what is set for slow_query_log_file, and try doing a tail -f on it in another window. That way you have instant feedback wether your settings work.

If you don't immeaditly see output, try lowering long_query_time, measured in seconds. Try flush logs; in case you see nothing and slowquery threshold is already set to 1 second.

Also set global log_queries_not_using_indexes = on; might help a lot.

oneliner for debian-sys-maint mysql user
posted on 2016-09-23 16:08
mysql -u debian-sys-maint -h localhost -p $(grep password /etc/mysql/debian.cnf | head -1 | awk '{print $3}') mysql
mysql 5.7 fix root user
posted on 2016-08-22 13:42

In mysql 5.7 the auth mechanism changed, documentation can be found in the official manual here.

Using the system root user (or sudo) you can connect to the mysql database with the mysql 'root' user via CLI. All other users will work, too.

In phpmyadmin, for example, however, all mysql users will work, but not the mysql 'root' user.

This comes from here:

$ mysql -Ne "select Host,User,plugin from mysql.user where user='root';"

+-----------+------+-----------------------+
| localhost | root | auth_socket           |
|  hostname | root | mysql_native_password |
+-----------+------+-----------------------+

To 'fix' this security feature, do:

mysql -Ne "update mysql.user set plugin='mysql_native_password' where User='root' and Host='localhost'; flush privileges;"

More on this can also be found here in the manual.

mysql: extract table dump from database dump
posted on 2016-08-10 09:08

Usually complete databases are dumped via mysqldump so you get a consistent backup. Dumping table after single table would only work if the database were made read-only during the backup, and is too much of a hazzle normally.

To restore a single table but not the whole database, the regular approach is to restore the dump to new database, and only dump the table you need.

However with huge dumps like 1GB and bigger this becomes cumbersome. Since a mysql dump are just all the SQL statements in plaintext, you can easily strip all other information via sed from the dump file:

sed -n -e "/DROP TABLE.*`my_table_i_want`/,/UNLOCK TABLES/p" mysql_dump_file.sql > my_table_i_want.sql

This will restore all necessary information for the table my_table_i_want from the dumpfile mysql_dump_file.sql into file my_table_i_want.sql.

mysql 5.7 reset root password
posted on 2016-08-04 14:04

Resetting the mysql root password changed with version 5.7, along with quite some other stuff.

If you have further trouble logging in with the local root account itself, these steps should fix all the problems.

  • stop mysql server (service mysql stop or ps aux | grep mysql to determine the PID and then kill -9 PID)
  • mysqld_safe --skip-grant-tables
  • mysql -Ne "UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE User = 'root';" to fix the root PW not working
  • mysql -Ne "UPDATE mysql.user SET authentication_string=password('YOURNEWPASSWORD') WHERE user='root';"
  • killall -9 mysqld_safe
  • service mysql start
mysql: mysqlcheck to test, repair and optimize all tables
posted on 2016-04-11 17:46:35

This is all you want:

mysqlcheck --auto-repair -o -A

-o is for optimizing, -A does it for all databases. Beware, might take longer than you think.

If you see in SHOW FULL PROCESSLIST; that mysql starts persisiting large temp tables, it may be in the course of locking your meta tables and thus block the whole database management system.

Kill the process then before it is too late.

A good indicator if something is awry is:

watch -n1 -d "mysql -e 'show full processlist;' | wc -l"
mysql: read .csv file into table
posted on 2016-03-01 07:48:37

Also sweet and simple, the compagnion to the opposite here:

load data infile '<FILENAME>.csv' into table <TABLE> fields terminated by ';' enclosed by '"' lines terminated by '\n' ignore 1 rows;"'

<FILENAME>.csv has to be located at /var/lib/mysql, if no other path is specified besides the filename.

typo3: password reset
posted on 2016-02-18 10:05:04

admin password for the backend through mysql

UPDATE be_users SET password=md5('your_new_password') WHERE username = 'admin';

reset install tool password

In [your_site]/typo3conf/LocalConfiguration.php do:

$TYPO3_CONF_VARS['BE']['installToolPassword'] = 'bacb98acf97e0b6112b1d1b650b84971';

This will give you the default password of joh316 again.

This stuff comes from the manual here.

mysql: dump table as .csv
posted on 2016-01-04 07:38:01

Sweet and simple:

select * into outfile '<FILENAME>.csv' fields terminated by ';' optionally enclosed by '"' lines terminated by '\n' from <TABLE>;

<FILENAME>.csv is the name of the saved file, which can be found at /var/lib/mysql afterwards. <TABLE> is the name name of the database table you want to export.

mysql: grants, revisited
posted on 2015-10-18 00:07:40

Another way to gather information about grants, can be seen here:

mysql --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql --skip-column-names -A | sed 's/$/;/g'

This assumes you have a .my.cnf in your /root / homefolder so you can access the mysql cli without a password. Else you have to pass -u and -p, too.

Pipe this command into less for viewing, or safe it into a file for exporting to another server. On the other server it can be piped directly into mysql, and they you have transferred all your users and access rights at once.

In case of transferring the userdata like this, beware of the root user in there, too. It will kill the current root password on the new server, if you don't clean it from the file prior to replaying it into mysql there.

Plesk 12 database overview
posted on 2015-08-11 18:40:06

Ever felt the need to dig deep into plesk data model?

Starting with 12.5, there will be some official documentation on this, until it's done see here.

PHP / MySQL Tuning
posted on 2015-07-22 05:42:35

For sqeezing a little bit more performance out of a php page backed with mysql, try:

/etc/mysql/my.cnf:

#max_execution_time = 30
max_execution_time  = 60

#memory_limit       = 128M
memory_limit        = 512M

php.ini:

#key_buffer         = 16M
key_buffer          = 32M
#query_cache_limit  = 8M
query_cache_limit   = 8M
#query_cache_size   = 16M
query_cache_size    = 64M

Commented lines are the default values for comparison.

MySQL: Check used storage engine
posted on 2015-07-13 15:32:01

Something to copy paste, in case you already have a .my.cnf for your root user with his password.

This only for tables you created:

less < <({ for i in $(mysql -e "show databases;" | cat | grep -v -e Database -e information_schema -e mysql -e performance_schema); do echo "--------------------$i--------------------";  mysql -e "use $i; show table status;"; done } | column -t)

This will show all tables, including the mysql ones:

less < <({ for i in $(mysql -e "show databases;" | cat | grep -v -e Database); do echo "--------------------$i--------------------";  mysql -e "use $i; show table status;"; done } | column -t)

To make it a little more readable, hitting -S in less turns or wordwrapping in less. Thus the lines which are too long are simply cut.

In a little more detail, this cannot be copy pasted in this form as it's missing the line break escapes, sorry this time not:

less < <(
            { 
                for i in $(mysql -e "show databases;" | 
                cat | 
                grep -v -e Database -e information_schema -e mysql -e performance_schema);
                do echo "--------------------$i--------------------"; 
                    mysql -e "use $i; show table status;";
                done 
            } | 
            column -t
        )

The cat piping is needed so the output will be without borders. I honestly have no idea why this cat here works the way it does. :)

mysql: show grants for database
posted on 2015-07-03 15:37:44

To show all grants for a single database in a proper overview:

select db 'DATABASE', host HOST, user USER from mysql.db where db = '<databasename>';

To quickly check all databases and all permissions:

select db,host,user from mysql.db;
Linux: website migration guide
posted on 2015-06-19 19:53:32

Migrating a website can be a tedious task, if you have problems keeping several things at once inside your head. This aims to solve this problem by presenting some proper guidelines.

Here we have a standard dynamic website with a mysql backend, served through an apache httpd.

For other databases/webservers the steps may differ in particular, but essentially this is the same theory everytime.

Mailmigration will as of now not be a part of this here, since it's gonna be long enough anyway.

Read this completely prior, as alternative ways are suggested sometimes.

preparations

This part is almost the most important, actual copying is usually not that hard if you know what you are doing. It's often harder to remember everything.

Before we start, the server can serve data of three kinds which are handled all the same way.

web data, just copy the website code
database, copy the database dump file
emails, copy the mailfiles

The server is accessed via the globally available...:

dns

Basically these are the things you have to copy/adjust so things will go smooth.

preparations

open questions

Putting most of these questions plus the answers to them into a spreadsheed is not the worst idea. Maybe I will come up with a shell one-liner to create a .csv later.

Also it is helpful if you are able to do FXP (transfer files from one host directly to the other, without temporary saving the data/files locally), if you do not have SSH access.

  • server access via ssh is possible?

  • ssh works via key? or password only?

  • root account? (a lot of this guide assumes root privileges, I might have missed points there are no alternatives)

  • if not, do you have all necessary account credentials for all folders etc.?

  • DO THESE WORK?

  • if no ssh, do you have ftp credentials?

  • do the credentials actually work?

  • do you get a database dump you can transfer? (If you cannot access the server, you can't make a dump.)

  • are the folder accurately named?

  • how BIG is the webfolder? (so how long will copying take?)

  • which database management system is used? (i.e. mysql or postgres)

  • database credentials for it are?

  • what is the database the site is using actually called?

  • just how BIG is the database? (and so how long will copying take?)

  • what domains are pointing to the server?

  • are these actually active?

  • and can you change the DNS RR?

  • what are the DNS TTL times?

  • is mailing configured?

  • don't forget the DNS MX RR/RR's while at the last point

DNS: aquiring information active resource records

For finding out about the dns, if you have several virtual hosts on the same machine, try grepping them all there.

When having an apache, grep all vhost files for ServerName and ServerAlias. Here's a kind-of snippet, which will work if your apache vhost configs are in default locations and indented:

\grep -e '^\s\+Server' /etc/apache2/sites-enabled/*

This shows only active sites, check sites-available if you have to migrate sites which are currently turned off, too.

The resulting list, if sanitized, can be piped on the shell and used with something like host/nslookup/echo + dig +short, to easily check which domains are still running. Check all the records, not just the A/AAAA (quad-A is ipv4, single-A is ipv4) records, also MX and whatever is set. If the exit code is non-zero, no dns anymore and less work for you. Providing a script here would not help much, since you should know what you are doing here anyway and it would most likely not help you much.

and maybe prepare the webserver, too

In case the apache config is, lets say, 'adventurous', do apache2ctl -S (Debian/Ubuntu) or httpd -S to see which domains are hosted, and in which file these are defined. Then search there for ServerName/ServerAlias directives.

If the webserver happens to have all vhosts defined in one huge file (which ist just... very not great), remove the configuration and place them into a separated file. In Debian-based Linuces you can use a2ensite <vhost-config-filename> / a2dissite <vhost-config-filename> to enable/disable single websites easily. On Redhat-based ones you create the symlinks to the configfolder apache is configured to load manually and delete them also by hand. (This isn't any different from what a2en/dissite do.) All this only for the sites you want to migrate.

Of course, you can just comment out the information on your vhosts from the config, but just... don't.

For other webservers all this is different, of course, but you get the idea.

DNS: get the domains and the website together, information-wise

Refer to the website via its main link. (apache ServerName from above.) But make sure to note all other aliases there, too. (apache ServerAlias from above.) Since you can only migrate one site after another, this helps to keep track. Write all this down, each alias in another row. Maybe put the inactive ones into an extra column there, too. Could be that these should be prolonged again, or were incorrectly set. (I.e. it did not point to the webserver when you checked.)

Write the set TTL into the next column, along with the current date. (Usually TTL is 86400, which means 24 hours, which is exactly how long it will take until your change to 1800 seconds becomes finally active. If the TTL was longer than 86400 for whatever reason, note that into your list, too!)

DNS: lower TTL the day before the migration

After having created a list and checked which domains are currently active, set the default TTL time to 1800. (Just don't go below, 30 mins are short while you do the migration. Also the registrar might prefer you not to.)

DNS: plan b in case you have dozens of websites to migrate

If you have A LOT of websites that should go from one server to the next, try migrating and testing everything (via entries in the hosts file). Then switch the ip's of the servers with each other. That way no dns changes are needed (except if you have dead domains), because this shit can become tedious, too.

TBD / todo

Nothing more here now, until i am motivated again to write more stuff up.

mysql: encoding overview
posted on 2015-06-11 17:12:48

check existing databases

To see how databases in mysql are created, this might be helpful:

SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;

The output looks like this:

mysql> SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;
+--------------------+---------+-------------------+
| database           | charset | collation         |
+--------------------+---------+-------------------+
| information_schema | utf8    | utf8_general_ci   |
| mysql              | latin1  | latin1_swedish_ci |
| performance_schema | utf8    | utf8_general_ci   |
+--------------------+---------+-------------------+
3 rows in set (0.00 sec)

mysql>

The above is from a fresh install on a debian 8 of an 5.5 mysql server, just for the record.

create new database

If I do not want to create a new DB with the standard latin1 encoding, I use this:

create database <databasename> character set utf8 collate utf8_unicode_ci;
mysql: show users (with proper syntax and host accesses)
posted on 2015-05-22 12:34:19

To have a proper user overview, use this:

SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) UserAccounts FROM mysql.user ORDER BY user;

Which will give you this:

mysql> SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) UserAccounts FROM mysql.user ORDER BY user;
+--------------------------------+
| UserAccounts                   |
+--------------------------------+
| 'debian-sys-maint'@'localhost' |
| 'root'@'localhost'             |
| 'root'@'my-hostname'           |
| 'root'@'127.0.0.1'             |
| 'root'@'::1'                   |
+--------------------------------+
5 rows in set (0.00 sec)
mysql: show table sizes
posted on 2015-05-22 12:07:28

To show all table sizes for a given database, ascending, so largest are shown last:

# change <"DB_NAME"> !

SELECT table_name AS "Tables", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
ORDER BY (data_length + index_length) ASC;

To query just for a single table from a given database:

# change <"DB_NAME"> and <"TABLE_NAME"> !

SELECT table_name AS "Table", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
 AND table_name = "$TABLE_NAME";

Found these gems on stackoverflow.

MySQL: restore single table from dump
posted on 2015-04-24 11:13:21

MySQL dumps are usually created from whole databases. But what if you only need a single table restored?

You could edit/sed/grep the dump for information on just this single one table (and hopefully don't fuck up), or let mysql do the work. Simply restore the dump to a test database and then dump just the table in question, so you can load just the table dump back into the production database.

Keep in mind, this might take ages if you have extremely large dumps.

In the following it is assumed you have a working .my.cnf so you do not have to enter the user and passwort with every shell call.

#create db
mysqladmin create NAME_OF_TEMP_DB

#replay full dump
mysql NAME_OF_TEMP_DB < fulldump.sql

#dump table in question
mysqldump NAME_OF_TEMP_DB TABLE_NAME > table_name.sql

#load tabledump back into production
mysql NAME_OF_PROD_DB < table_name.sql

So simply mysqladmin - mysql - mysqldump - mysql and you are done. :)

mysql: command history
posted on 2015-03-27 10:25:58

To view the complete mysql command history from the shell prompt:

less ~/.mysql_history

To view the complete mysql commmand history from withing the mysql CLI:

system cat ~/.mysql_history
mysql: create admin user
posted on 2015-03-24 21:52:43

Open mysql cli:

create user '<user>'@'localhost' identified by '<pw>';
grant all privileges on *.* to '<user>'@'localhost' with grant option;
create user '<user>'@'%' identified by '<pw>';
grant all privileges on *.* to '<user>'@'%' with grant option;
flush privileges;

This looks strange, but to be able to connect as user from everywhere, you need it. See here and search for 'monty'.

mysql: output layout
posted on 2015-03-04 17:51:07

For big mysql tables with a lot of columns, the regular screen output is kind of hard to read at times.

Regularily you call queries like this:

select * from <tablename>;

There are several ways to fix this:

Within the client:

select * from <tablename>\G

At client startup:

## always use alternative output
mysql --vertical

## choose output depending on console width
mysql --auto-vertical-output

How does this look?

Regular:

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

Alternative:

mysql> show tables\G
*************************** 1. row ***************************
Tables_in_mysql: columns_priv
*************************** 2. row ***************************
Tables_in_mysql: db
*************************** 3. row ***************************
Tables_in_mysql: event
*************************** 4. row ***************************
Tables_in_mysql: func
*************************** 5. row ***************************
Tables_in_mysql: general_log
*************************** 6. row ***************************
Tables_in_mysql: help_category
*************************** 7. row ***************************
Tables_in_mysql: help_keyword
*************************** 8. row ***************************
Tables_in_mysql: help_relation
*************************** 9. row ***************************
Tables_in_mysql: help_topic
*************************** 10. row ***************************
Tables_in_mysql: host
*************************** 11. row ***************************
Tables_in_mysql: ndb_binlog_index
*************************** 12. row ***************************
Tables_in_mysql: plugin
*************************** 13. row ***************************
Tables_in_mysql: proc
*************************** 14. row ***************************
Tables_in_mysql: procs_priv
*************************** 15. row ***************************
Tables_in_mysql: proxies_priv
*************************** 16. row ***************************
Tables_in_mysql: servers
*************************** 17. row ***************************
Tables_in_mysql: slow_log
*************************** 18. row ***************************
Tables_in_mysql: tables_priv
*************************** 19. row ***************************
Tables_in_mysql: time_zone
*************************** 20. row ***************************
Tables_in_mysql: time_zone_leap_second
*************************** 21. row ***************************
Tables_in_mysql: time_zone_name
*************************** 22. row ***************************
Tables_in_mysql: time_zone_transition
*************************** 23. row ***************************
Tables_in_mysql: time_zone_transition_type
*************************** 24. row ***************************
Tables_in_mysql: user
24 rows in set (0.00 sec)
Plesk: mysql admin password
posted on 2015-02-25 16:36:11

To access a plesk's mysql database, you need the password plesk creates by itself.

Either get it in plaintext:

/usr/local/psa/bin/admin --show-password

Or just access the mysql db client with this line:

mysql -uadmin -p$(cat /etc/psa/.psa.shadow)
typo3: access without password
posted on 2015-02-23 10:35:22

To gain access to typo3 backend without having a working set of credentials, you might either try using the mysql client and directly add the user to the database, but this is kind of unwieldy.

Easier it is to use the typo3 install tool.

On a proper install this can be accessed via http://<yourdomain.name>/typo3/install from your browser, where you will be prompted for the install password. Since it is extremely likely that you do not know it anymore, change it to one you know. Also the install tool is very likely deactivated. typo3 may tell you in the login mask, which file to create on the server so you can access the web interface afterwards. For me it was to create an empty file called ENABLE_INSTALL_TOOL in the typo3conf folder of the typo3 install.

To get a working password, you have to swap the md5 hash of the password in the <path-to-your-typo3-install>/typo3conf/localconf, or in typo3 versions prior to 6.0 in the localconf.php file. Have a look at the typo3 documentation what to do exactly, as this can change with new typo3 releases.

Sidenote:
To get a working hash, simple enter the password you want in to the install tool webmask. The login will fail and the tool will tell you the hash of the password you wanted to use.

Once you are logged in, choose the 'Database Analyser' menu, in there you should be able to create a new 'admin' user.

With that one you can login into the backend.

Do not forget to cleanup after you (remove the ENABLE_INSTALL_TOOL file from the typo3conf folder).

mysql: show database sizes
posted on 2015-02-05 10:07:28

Try running the following query:

SELECT table_schema                                        "DB Name", 
   Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM   information_schema.tables 
GROUP  BY table_schema; 

This gives you something along this lines:

mysql> SELECT table_schema                                        "DB Name", 
    ->    Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
    -> FROM   information_schema.tables 
    -> GROUP  BY table_schema; 
+-----------------------+---------------+
| DB Name               | DB Size in MB |
+-----------------------+---------------+
| information_schema    |           0.0 |
| mysql                 |           0.7 |
| provisioning_test     |          46.3 |
| app_example           |          24.4 |
| app_production        |           0.1 |
| app_userdata          |         178.7 |
| performance_schema    |           0.0 |
| phpmyadmin            |           0.3 |
| other                 |          59.4 |
+-----------------------+---------------+
9 rows in set (0.08 sec)
mysql reset root password
posted on 2014-09-08 11:27:38

To reset mysql passwords, connect from the commandline to the mysql daemon.

But like this:

  1. service mysql stop
  2. mysqld_safe --skip-grant-tables --skip-networking. That way no pw checks are made, and network connections are disabled, only localhost connections are enabled. (Security!)
  3. change password: mysql -Ne "update mysql.user set password=password('this_is_the_new_password') where user='root'; flush privileges;"
  4. stop mysqld with killall -9 mysqld_safe
  5. service mysql start
  6. try the account with the new password

If it is not working, service mysql stop again, start mysqld_safe --skip-grant-tables --skip-networking and fix the password, as you likely had a copy-paste or typing in there the last time.

MySQL-over-SSL
posted on 2014-08-08 14:45:03

How to use SSL/TLS for mysql connections? We shall see.

This guide assumes you have mysqld already installed and have root access on the box you are working on. All this was done a debian 7 install.

check if your mysqld supports it

This means, if the mysql install was compiled with the right flags.

  • connect to mysql
  • show variables like 'have_ssl';
  • DISABLED = capable, but was not started with the option on
  • YES = SSL capable and enabled

Example:

[sjas@box ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 65
Server version: 5.5.38-0+wheezy1 (Debian)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'have_ssl';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_ssl      | DISABLED |
+---------------+----------+
1 row in set (0.00 sec)

mysql> quit
Bye
[sjas@box ~]$

cert 101

To use a public key infrastructure here, you have to have a root certificate (coming from a CA, which is short for certificate authority).

Thus a private key for the CA is generated and a certificate.

Next step is creating the server private key, and creating a certificate request from it. This request is used with the CA cert and the CA key, to create the actual server certificate.

This procedure is repeated for the client.


Finally, on client side, to use SSL/TLS, you have to use these: The CA cert, the client cert and the client key

Finally, on server side, to use SSL/TLS, you have to use these: The CA cert, the server cert and the server key

All these will be self-signed (self-generated, and not signed by an actual CA like Thawte or Digicert) and created by the next script.

create certificates

This script is handy, put it into a file and make it executable for usage:

#!/bin/bash

echo $'\e[1;33m''clean environment'$'\e[0m'
rm -rf mynewcerts
mkdir mynewcerts
cd mynewcerts

echo $'\e[1;33m''NOTE:'$'\e[0m'
echo $'\e[1;33m''USE DIFFERENT COMMON NAMES'$'\e[0m'
echo $'\e[1;33m''FOR ALL CERTS.'$'\e[0m'

echo $'\e[1;33m''create ca cert'$'\e[0m'
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem

echo $'\e[1;33m''Create server certificate, remove passphrase, and sign it'$'\e[0m'
echo $'\e[1;33m''server-cert.pem = public key, server-key.pem = private key'$'\e[0m'
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

echo $'\e[1;33m''Create client certificate, remove passphrase, and sign it'$'\e[0m'
echo $'\e[1;33m''client-cert.pem = public key, client-key.pem = private key'$'\e[0m'
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

echo $'\e[1;33m''verify certs'$'\e[0m'
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

echo $'\e[1;33m''move server files to /etc/mysql/ssl'$'\e[0m'
mkdir - /etc/mysql/ssl
cp ca-cert.pem server-cert.pem server-key.pem /etc/mysql/ssl

echo $'\e[1;33m''TESTING Howto:'$'\e[0m'
echo $'\e[1;33m''service mysql stop'$'\e[0m'
echo $'\e[1;33m''mysqld --ssl-ca=/etc/mysql/ssl/ca-cert.pem --ssl-cert=/etc/mysql/ssl/server-cert.pem --ssl-key=/etc/mysql/ssl/server-key.pem'$'\e[0m'
echo $'\e[1;33m''Then in second console window:'$'\e[0m'
echo $'\e[1;33m''connect to this server, cd into mynewcerts folder'$'\e[0m'
echo $'\e[1;33m''mysql --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem  --ssl-key=client-key.pem -uroot -p'$'\e[0m'

This will create and do:

  • a CA key and certificate
  • a server privatekey, cert request and cert
  • and a client privatekey, cert and request
  • move the server-files to /etc/mysql/ssl

The requests are actually not important, once the certificates are generated. They are left in, in case a recreation of the certificates, i.e. with new CA data, is to be made.

Leave the passphrases blank and look out, so you have differing common names. If you use the same common names, you will have problems later on.

testing

Both these can be done on the same server.

server side

On server side, you have to use these files:

  • ca-cert.pem
  • server-cert.pem
  • server-key.pem

Create a directory in your mysql folder, here named /etc/mysql/ssl, and put these files into there.

Then stop mysqld, if running:

$ service mysql stop
$ mysqld --ssl-ca=/etc/mysql/ssl/ca-cert.pem --ssl-cert=/etc/mysql/ssl/server-cert.pem --ssl-key=/etc/mysql/ssl/server-key.pem

The MySQL server has to be properly started with these SSL options, else SSL would be turned of on server-side. On how to automate this, see the end of this guide.

client side

On client side, you have to use these files:

  • ca-cert.pem
  • client-cert.pem
  • client-key.pem

So from within the mynewcerts folder the script just created, run from a second shell this:

$ mysql --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem  --ssl-key=client-key.pem -uroot -p

After entering your password, you are at the mysql prompt. Use \s to check the output.

You might get something along these lines:

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.2

Connection id:          40
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.38-0+wheezy1 (Debian)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 1 hour 29 min 28 sec

Threads: 1  Questions: 116  Slow queries: 0  Opens: 171  Flush tables: 1  Open tables: 41  Queries per second avg: 0.021
--------------

mysql>

If the SSL: ... line actually shows a cipher (and is not just empty), your setup works.

\q to exit from the mysql shell, then pkill mysql to kill the mysql server you just started by hand (see server section above).

The above can be done shorter, try this line:

mysql --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem  --ssl-key=client-key.pem -uroot -p -sss -e \s | grep SSL

proper setup

To have the mysqld starting properly, you have to pass the SSL options to the init script.

This is easiest done like this: (Another copy&paste section, this can be pasted directly to the shell.)

cat <<EOHD >> /etc/mysql/conf.d/ssl.cnf
[mysqld]
ssl-ca   = /etc/mysql/ssl/ca-cert.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key  = /etc/mysql/ssl/server-key.pem
EOHD

The /etc/mysql/my.cnf should include the following line:

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d

... so the previously generated file will work. Then do:

service mysql stop  ## in case it started somehow
service mysql start

Of course, you should test your work again via the mysql client (see client section above and look at the output of \s).

Otherwise, pass the client privkey, client cert and CA cert to the machines you actually want to secure and be happy you have encrypted mysql traffic.

force SSL usage

Further you can also force users to use SSL, when creating them within mysql.

From the mysql prompt:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'%' IDENTIFIED BY 'password-to-change' REQUIRE SSL WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

Then this will work: (From within the folder where the certs are located, of course.)

$ mysql --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -ussluser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 57
...

but not this:

$ mysql -u ssluser -p
Enter password:
ERROR 1045 (28000): Access denied for user 'ssluser'@'localhost' (using password: YES)

Keep in mind, that the ssluser account created above is intended to replace the root account. That is the reason he got rights for everything (*.*) and can grant/revoke rights (WITH GRANT OPTION). Use a more fine-granular approach if you want to create accounts for your customers, else they could kill your DB as they wish.

remote access

In order to tell mysql to grant access to from remote machines:

$ vim /etc/mysql/my.cnf

and comment out:

bind-address = 127.0.0.1

exchanging keys and certs between machines

At last the note, exchanging privatekeys should never take place over unencrypted channels. Try scp, sftp or mailing the three files via a password encrypted compressed archive (zip, tar, 7z, ...) and passing the password through a separate channel.

Everything else is just plain bad style.

MySQL Debian root password reset
posted on 2014-08-04 16:24:52

If you have lost your mysql root password, and you happen to be on a debian system, you have luck:

[sjas@test ~]# cat /etc/mysql/debian.cnf 
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = r51fkvVRogY4i5oj
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = R51fkvVRogY4i5oj
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

On debian, a system user with the same rights as mysql's root is created upon install. This one is used for mysql updates, for example.

So you can login into mysql, using the password from above:

$ sudo mysql -u debian-sys-maint -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.38-0+wheezy1 (Debian)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> update mysql.user set Password=PASSWORD('my-new-and-secret-password') where user='root'; flush privileges; exit;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Bye
$

In this example the root PW got set to my-new-and-secret-password.

This blog covers .csv, .htaccess, .pfx, .vmx, /etc/crypttab, /etc/network/interfaces, /etc/sudoers, /proc, 10.04, 14.04, AS, ASA, ControlPanel, DS1054Z, GPT, HWR, Hyper-V, IPSEC, KVM, LSI, LVM, LXC, MBR, MTU, MegaCli, PHP, PKI, R, RAID, S.M.A.R.T., SNMP, SSD, SSL, TLS, TRIM, VEEAM, VMware, VServer, VirtualBox, Virtuozzo, XenServer, acpi, adaptec, algorithm, ansible, apache, apachebench, apple, arcconf, arch, architecture, areca, arping, asa, asdm, awk, backup, bandit, bar, bash, benchmarking, binding, bitrate, blackarmor, blowfish, bochs, bond, bonding, booknotes, bootable, bsd, btrfs, buffer, c-states, cache, caching, ccl, centos, certificate, certtool, cgdisk, cheatsheet, chrome, chroot, cisco, clamav, cli, clp, clush, cluster, coleslaw, colorscheme, common lisp, console, container, containers, controller, cron, cryptsetup, csync2, cu, cups, cygwin, d-states, database, date, db2, dcfldd, dcim, dd, debian, debug, debugger, debugging, decimal, desktop, df, dhclient, dhcp, diff, dig, display manager, dm-crypt, dmesg, dmidecode, dns, docker, dos, drivers, dtrace, dtrace4linux, du, dynamictracing, e2fsck, eBPF, ebook, efi, egrep, emacs, encoding, env, error, ess, esx, esxcli, esxi, ethtool, evil, expect, exportfs, factory reset, factory_reset, factoryreset, fail2ban, fbsd, fedora, file, filesystem, find, fio, firewall, firmware, fish, flashrom, forensics, free, freebsd, freedos, fritzbox, fsck, fstrim, ftp, ftps, g-states, gentoo, ghostscript, git, git-filter-branch, github, gitolite, gnutls, gradle, grep, grml, grub, grub2, guacamole, hardware, haskell, hdd, hdparm, hellowor, hex, hexdump, history, howto, htop, htpasswd, http, httpd, https, i3, icmp, ifenslave, iftop, iis, imagemagick, imap, imaps, init, innoDB, innodb, inodes, intel, ioncube, ios, iostat, ip, iperf, iphone, ipmi, ipmitool, iproute2, ipsec, iptables, ipv6, irc, irssi, iw, iwconfig, iwlist, iwlwifi, jailbreak, jails, java, javascript, javaws, js, juniper, junit, kali, kde, kemp, kernel, keyremap, kill, kpartx, krypton, lacp, lamp, languages, ldap, ldapsearch, less, leviathan, liero, lightning, links, linux, linuxin3months, lisp, list, livedisk, lmctfy, loadbalancing, locale, log, logrotate, looback, loopback, losetup, lsblk, lsi, lsof, lsusb, lsyncd, luks, lvextend, lvm, lvm2, lvreduce, lxc, lxde, macbook, macro, magento, mailclient, mailing, mailq, manpages, markdown, mbr, mdadm, megacli, micro sd, microsoft, minicom, mkfs, mktemp, mod_pagespeed, mod_proxy, modbus, modprobe, mount, mouse, movement, mpstat, multitasking, myISAM, mysql, mysql 5.7, mysql workbench, mysqlcheck, mysqldump, nagios, nas, nat, nc, netfilter, networking, nfs, nginx, nmap, nocaps, nodejs, numberingsystem, numbers, od, onyx, opcode-cache, openVZ, openlierox, openssl, openvpn, openvswitch, openwrt, oracle linux, org-mode, os, oscilloscope, overview, parallel, parameter expansion, parted, partitioning, passwd, patch, pdf, performance, pfsense, php, php7, phpmyadmin, pi, pidgin, pidstat, pins, pkill, plesk, plugin, posix, postfix, postfixadmin, postgres, postgresql, poudriere, powershell, preview, profiling, prompt, proxmox, ps, puppet, pv, pvecm, pvresize, python, qemu, qemu-img, qm, qmrestore, quicklisp, r, racktables, raid, raspberry pi, raspberrypi, raspbian, rbpi, rdp, redhat, redirect, registry, requirements, resize2fs, rewrite, rewrites, rhel, rigol, roccat, routing, rs0485, rs232, rsync, s-states, s_client, samba, sar, sata, sbcl, scite, scp, screen, scripting, seafile, seagate, security, sed, serial, serial port, setup, sftp, sg300, shell, shopware, shortcuts, showmount, signals, slattach, slip, slow-query-log, smbclient, snmpget, snmpwalk, software RAID, software raid, softwareraid, sophos, spacemacs, spam, specification, speedport, spi, sqlite, squid, ssd, ssh, ssh-add, sshd, ssl, stats, storage, strace, stronswan, su, submodules, subzone, sudo, sudoers, sup, swaks, swap, switch, switching, synaptics, synergy, sysfs, systemd, systemtap, tar, tcpdump, tcsh, tee, telnet, terminal, terminator, testdisk, testing, throughput, tmux, todo, tomcat, top, tput, trafficshaping, ttl, tuning, tunnel, tunneling, typo3, uboot, ubuntu, ubuntu 16.04, udev, uefi, ulimit, uname, unetbootin, unit testing, upstart, uptime, usb, usbstick, utf8, utm, utm 220, ux305, vcs, vgchange, vim, vimdiff, virtualbox, virtualization, visual studio code, vlan, vmstat, vmware, vnc, vncviewer, voltage, vpn, vsphere, vzdump, w, w701, wakeonlan, wargames, web, webdav, weechat, wget, whois, wicd, wifi, windowmanager, windows, wine, wireshark, wpa, wpa_passphrase, wpa_supplicant, x2x, xfce, xfreerdp, xmodem, xterm, xxd, yum, zones, zsh

View posts from 2017-03, 2017-02, 2017-01, 2016-12, 2016-11, 2016-10, 2016-09, 2016-08, 2016-07, 2016-06, 2016-05, 2016-04, 2016-03, 2016-02, 2016-01, 2015-12, 2015-11, 2015-10, 2015-09, 2015-08, 2015-07, 2015-06, 2015-05, 2015-04, 2015-03, 2015-02, 2015-01, 2014-12, 2014-11, 2014-10, 2014-09, 2014-08, 2014-07, 2014-06, 2014-05, 2014-04, 2014-03, 2014-01, 2013-12, 2013-11, 2013-10


Unless otherwise credited all material Creative Commons License by sjas