Tag Archives: MySQL

Export data from HDFS to MySQL

First create the DB and table where you want to populate.

user@computer:$ echo "create database staff2; use staff2; CREATE TABLE editorial (id INT(100) unsigned not null AUTO_INCREMENT, name VARCHAR(20), email VARCHAR(20), primary key (id));" | mysql -u root -p

Once done, we have the data we want to copy in HDFS.

user@computer:$ hdfs dfs -cat /home/training/staff/editorial/part-m-*
1,Peter,peter@example.com
2,Jack,jack@example.com

Now dump into MySQL using sqoop.

user@computer:$ sqoop export --connect jdbc:mysql://localhost/staff2 --username root -P --table editorial --export-dir /home/training/staff/editorial
17/02/27 12:51:56 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5-cdh5.2.0
Enter password:
17/02/27 12:51:58 INFO manager.SqlManager: Using default fetchSize of 1000
17/02/27 12:51:58 INFO tool.CodeGenTool: Beginning code generation
17/02/27 12:51:59 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `editorial` AS t LIMIT 1
17/02/27 12:51:59 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `editorial` AS t LIMIT 1
17/02/27 12:51:59 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/lib/hadoop-0.20-mapreduce
Note: /tmp/sqoop-training/compile/e560499b42a9738bbc5ef127712adc7b/editorial.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
17/02/27 12:52:03 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-training/compile/e560499b42a9738bbc5ef127712adc7b/editorial.jar
17/02/27 12:52:03 INFO mapreduce.ExportJobBase: Beginning export of editorial
17/02/27 12:52:06 WARN mapred.JobClient: Use GenericOptionsParser for parsing the arguments. Applications should implement Tool for the same.
17/02/27 12:52:08 INFO input.FileInputFormat: Total input paths to process : 2
17/02/27 12:52:08 INFO input.FileInputFormat: Total input paths to process : 2
17/02/27 12:52:09 INFO mapred.JobClient: Running job: job_201702221239_0006
17/02/27 12:52:10 INFO mapred.JobClient: map 0% reduce 0%
17/02/27 12:52:31 INFO mapred.JobClient: map 50% reduce 0%
17/02/27 12:52:45 INFO mapred.JobClient: map 100% reduce 0%
17/02/27 12:52:49 INFO mapred.JobClient: Job complete: job_201702221239_0006
17/02/27 12:52:49 INFO mapred.JobClient: Counters: 24
17/02/27 12:52:49 INFO mapred.JobClient: File System Counters
17/02/27 12:52:49 INFO mapred.JobClient: FILE: Number of bytes read=0
17/02/27 12:52:49 INFO mapred.JobClient: FILE: Number of bytes written=1176756
17/02/27 12:52:49 INFO mapred.JobClient: FILE: Number of read operations=0
17/02/27 12:52:49 INFO mapred.JobClient: FILE: Number of large read operations=0
17/02/27 12:52:49 INFO mapred.JobClient: FILE: Number of write operations=0
17/02/27 12:52:49 INFO mapred.JobClient: HDFS: Number of bytes read=759
17/02/27 12:52:49 INFO mapred.JobClient: HDFS: Number of bytes written=0
17/02/27 12:52:49 INFO mapred.JobClient: HDFS: Number of read operations=19
17/02/27 12:52:49 INFO mapred.JobClient: HDFS: Number of large read operations=0
17/02/27 12:52:49 INFO mapred.JobClient: HDFS: Number of write operations=0
17/02/27 12:52:49 INFO mapred.JobClient: Job Counters
17/02/27 12:52:49 INFO mapred.JobClient: Launched map tasks=4
17/02/27 12:52:49 INFO mapred.JobClient: Data-local map tasks=4
17/02/27 12:52:49 INFO mapred.JobClient: Total time spent by all maps in occupied slots (ms)=64216
17/02/27 12:52:49 INFO mapred.JobClient: Total time spent by all reduces in occupied slots (ms)=0
17/02/27 12:52:49 INFO mapred.JobClient: Total time spent by all maps waiting after reserving slots (ms)=0
17/02/27 12:52:49 INFO mapred.JobClient: Total time spent by all reduces waiting after reserving slots (ms)=0
17/02/27 12:52:49 INFO mapred.JobClient: Map-Reduce Framework
17/02/27 12:52:49 INFO mapred.JobClient: Map input records=2
17/02/27 12:52:49 INFO mapred.JobClient: Map output records=2
17/02/27 12:52:49 INFO mapred.JobClient: Input split bytes=661
17/02/27 12:52:49 INFO mapred.JobClient: Spilled Records=0
17/02/27 12:52:49 INFO mapred.JobClient: CPU time spent (ms)=3390
17/02/27 12:52:49 INFO mapred.JobClient: Physical memory (bytes) snapshot=422584320
17/02/27 12:52:49 INFO mapred.JobClient: Virtual memory (bytes) snapshot=2940895232
17/02/27 12:52:49 INFO mapred.JobClient: Total committed heap usage (bytes)=127401984
17/02/27 12:52:49 INFO mapreduce.ExportJobBase: Transferred 759 bytes in 42.9426 seconds (17.6748 bytes/sec)
17/02/27 12:52:49 INFO mapreduce.ExportJobBase: Exported 2 records.

Now we can see the content in MySQL DB named staff2.

user@computer:$ echo "use staff2; SELECT * FROM editorial;" | mysql -u root -p
Enter password:
id name email
1 Peter peter@example.com
2 Jack jack@example.com

Setting replication MySQL version 5.5

So the other day I performed an upgrade of MySQL on a linode and notice that it wont start if I kept my old my.cnf file.

[11:47:06] user@linode1: ~ $ echo 'SHOW VARIABLES LIKE "%version%";' | mysql -u username -ppassword | grep innodb
innodb_version  5.5.31
[11:47:20] user@linode1: ~ $ 
131022 08:17:56 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
131022  8:17:56 [ERROR] An old style --language value with language specific part detected: /usr/share/mysql/english/
131022  8:17:56 [ERROR] Use --lc-messages-dir without language specific part instead.
131022  8:17:56 [Note] Plugin 'FEDERATED' is disabled.
131022  8:17:56 InnoDB: The InnoDB memory heap is disabled
131022  8:17:56 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131022  8:17:56 InnoDB: Compressed tables use zlib 1.2.7
131022  8:17:56 InnoDB: Using Linux native AIO
131022  8:17:56 InnoDB: Initializing buffer pool, size = 128.0M
131022  8:17:56 InnoDB: Completed initialization of buffer pool
131022  8:17:56 InnoDB: highest supported file format is Barracuda.
131022  8:17:56  InnoDB: Waiting for the background threads to start
131022  8:17:57 InnoDB: 5.5.31 started; log sequence number 1678395
131022  8:17:57 [ERROR] /usr/sbin/mysqld: unknown variable 'master-host=192.168.140.120'
131022  8:17:57 [ERROR] Aborting

Thing is the replication entries in my.cnf need to be removed/commented out and salve needs to be set up from MySQL console.

So here is my new my.cnf.

[11:34:25] xavi@linode1: ~ $ sudo grep -v “^#” /etc/mysql/my.cnf | grep -v “^$”

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
[mysqld]
server-id       = 2
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-external-locking
bind-address            = 192.168.137.234
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
myisam-recover          = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
expire_logs_days        = 10
max_binlog_size         = 100M
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M
[mysql]
[isamchk]
key_buffer              = 16M
!includedir /etc/mysql/conf.d/

[11:34:37] xavi@linode1: ~ $

We load the above file or similar depending on your configuration. Now to configure slave we log into the MySQL CLI.


mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST=’192.168.140.120′, MASTER_USER=’replication-user’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000747′, MASTER_LOG_POS=75797;
mysql> START SLAVE;
[/text]

That’s it.

‘Access denied for user ‘debian-sys-maint’@’localhost’

So the other day I made an aptitude safe-upgrade and MySQL was upgraded. Thing is I made some changes and when I tried to restart MySQL I saw the following error.

user@server:~$ sudo /etc/init.d/mysql restart
[sudo] password for user:
Stopping MySQL database server: mysqld failed!
Starting MySQL database server: mysqld already running.
/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)’
user@server:~$

Well fixing this error is fixed changing the password for MySQL user debian-sys-maint. We will retrieve the password from the file /etc/mysql/debian.cnf.


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

We backup the MySQL databases.


user@server:~$ mysqldump -u root -p –all-databases > /tmp/backup.sql
Enter password:
user@server:~$

Now lets change the debian-sys-maint password in the database.

mysql>GRANT ALL PRIVILEGES ON *.* TO ‘debian-sys-maint’@’localhost’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;

Now we should be able to safely restart the database server.

user@server:~$ sudo /etc/init.d/mysql restart
[sudo] password for user:
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
user@server:~$

References:

  1. Ubuntuforums

Apache MySQL authentication

Lets explain how to configure Apache MySQL authentication. We will suppose apache web server is already up and running on the server.
First we need to install auth_mysql module.

$ sudo aptitude search libapache2-mod-auth-mysql
p   libapache2-mod-auth-mysql       - Apache 2 module for MySQL authentication  
$ sudo aptitude install libapache2-mod-auth-mysql

Continue reading