MySQL

How to Connect MySQL from SQL Developer

Posted on
Connect MySQL from SQL Developer 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 […]
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 myqld, you can set the variable at run-time. The unit is bytes.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      |+————————-+————+| innodb_buffer_pool_size | 5368709120 |+————————-+————+1 row in set (0.00 sec)Please […]
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 […]
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 […]