MySQL

How SQL Developer Connect MySQL

Posted on
SQL Developer + MySQL By default, SQL developer can be used to connect only Oracle databases. As you can see, there’s only Oracle tab in the connection option. Luckily, SQL developer provides a very special feature that can import the third party JDBC driver into SQL developer. So that we can connect the third party […]
MySQL

How to Fetch Max Value in MySQL

Posted on
Fetch Row with Max Value On an online shopping website, we’d like to show visitors the most recent items added to the table items, which were logged in a column called added_time in our case. In this post, I will show you two ways that can fetch the row which has the max value of […]
MySQL

How to Execute SQL File in MySQL

Posted on
Just like we said in the previous post: How to Backup MySQL Database, mysqldump exports a set of executable SQL statements that can recreate the whole database if necessary. We can save the outputs as a SQL file which is a plain text file for any future restorations. Now here comes the question: How can […]
MySQL

How to Backup MySQL Database

Posted on
Backup MySQL Database MySQL provides mysqldump utility to let us dump SQL statements that can recreate the whole database. We can output the SQL statements to a plain text file, furthermore, a compressed file, or we can pipe the output to feed any other live database. Here in this post, we introduce some use cases […]
MySQL

How to Make a Long Text Column Unique

Posted on
You may have known that unique index on a string-typed column has limitation on prefix length, which can’t guarantee the uniqueness if the length of string was larger than the limit.MySQL :: MySQL 5.7 Reference Manual :: 14.8.1.7 Limits on InnoDB TablesThe index key prefix length limit is 767 bytes for InnoDB tables that use […]
MySQL

How to Change innodb_buffer_pool_size Online

Posted on
If you’d like to raise innodb_buffer_pool_size without restarting mysqld, you can set the variable at run-time. The size unit is in byte. mysql> set global innodb_buffer_pool_size = 5368709120; Query OK, 0 rows affected (0.01 sec) mysql> show variables like ‘innodb_buffer_pool_size’; +————————-+————+ | Variable_name           | Value      | +————————-+————+ | […]
Linux

How to Migrate Local Databases to Remote Host or Vice Versa Without Dump Files

Posted on
For migrating a remote database, you can dump and pipe the whole database from remote server to local server via ssh tunnel without intermediate dump files.[root@test ~]# ssh 10.10.10.1 “mysqldump –routines -u root -p’xxxxxxxx’ chicago_db” | mysql -u root -p’xxxxxxxx’ -D boston_dbVice versa, you can migrate a local database to the remote server.[root@test ~]# mysqldump […]
MySQL

How to Resolve MySQL Too many connections

Posted on
MySQL Too many connections Recently, I saw some blank pages on my website without any error, especially when the web server was very busy. I suspected that somewhere in PHP codes caught the exception and threw nothing. So I decided to display the error on the screen and saw MySQL too many connections error like […]
LAMP

How to Install Newest LAMP on CentOS 7.2 Minimal (1/4) – Install MySQL 5.7

Posted on
Install official yum repository.[root@test ~]# rpm -Uvh https://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpmRetrieving https://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpmwarning: /var/tmp/rpm-tmp.aF6L5J: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYPreparing…                          ################################# [100%]Updating / installing…   1:mysql57-community-release-el7-7  ################################# [100%]Let’s see what version we can have.[root@test ~]# yum info mysql-community-serverLoaded plugins: fastestmirrormysql-connectors-community             […]
MySQL

How to Backup MySQL Data and Stored Procedures

Posted on
By default, mysqldump only backup the data from which database indicated in the command line. For stored procedures, You have to enable the backup by adding an option –routines in the mysqldump command line. For example:[root@test ~]# mysqldump –routines -u root -p database_name > dump.sqlWhich routines oly include:ProceduresFunctionsIf you additionally need events to be backed […]