Tag Archives: MySQL

Export data from HDFS to MySQL

Leave a reply

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

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.

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.

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.

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

Leave a reply

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’
			

										

					Leave a reply				

					


				

			
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
			

										

					Leave a reply				

					


				

			
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 →